Technical Article

Refresh all views in a database

,

Assume that the tables in a database are changed in a way that would affect the definition of any views that are created on it. To help solve this, you will need to refresh the views in the database.

If you have a requirement to refresh all the views in a database, use the script. Change the database name at the top with your required database name and run the script. It should work.

Also if you have any requirement to refresh all views in all the databases, then use a cursor and run through the script. Fairly simple!!!

USE <>
GO
DECLARE @sqlcmd NVARCHAR(MAX) = ''
SELECT @sqlcmd = @sqlcmd +  'EXEC sp_refreshview ''' + name + ''';
' 
FROM sys.objects AS so 
WHERE so.type = 'V' 

SELECT @sqlcmd

--EXEC(@sqlcmd)

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.75 (4)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.75 (4)

You rated this post out of 5. Change rating

Related content

External Article

Looking at VIEWs, Close Up

Views in SQL can be difficult. It isn't easy to judge when to use them, It isn't always obvious how to determine if a view can be indexed or if it is updateable. Joe Celko takes a tricky topic and comes up with some helpful guidelines.

2016-06-07

6,986 reads