Howtos / Articles

Refreshing All Views After Table Structure Changes in Microsoft SQL

After making structure changes to a database table, you need to update any views that may be using those tables, otherwise the views may reference the wrong field, and cause all sorts of issues.

The T-SQL script below will refresh all views on a database.

Run this in a new query window in Microsoft SQL Server Management Studio.

Make sure the database you wish to refresh is selected.

DECLARE @ViewName VARCHAR(256)
DECLARE cViews CURSOR READ_ONLY FOR SELECT name from sys.views
OPEN cViews
FETCH NEXT FROM cViews INTO @ViewName
WHILE @@FETCH_STATUS != -1
BEGIN
	EXEC SP_REFRESHVIEW @ViewName
	PRINT 'View ''' + @ViewName + ''' has been refreshed.'
	FETCH NEXT FROM cViews INTO @ViewName
END
CLOSE cViews
DEALLOCATE cViews


Share this howto article!
  • Facebook
  • Twitter
  • Reddit
  • email

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>