This is a simple little script, but may come in handy for some. During our database upgrades we always run a series of utility steps to make sure our code is in sync and functioning properly based on the revisions that have been made for each release. This script is one of those run to recompile all views to catch any "SELECT *" views, or views where the underlying schema has changed and we missed changing the view.
This can be added as one of your steps to your deployment SQL.
----------------------------------------------------------------------------- -- REFRESH ALL VIEWS ----------------------------------------------------------------------------- SET NOCOUNT ON DECLARE @SQL varchar(max) = '' SELECT @SQL = @SQL + 'print ''Refreshing --> ' + name + ''' EXEC sp_refreshview ' + name + '; ' FROM sysobjects WHERE type = 'V' and name like 'vw_%' --< condition to select all views, may vary by your standards --SELECT @SQL EXEC(@SQL) go
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
In this tip Aaron Bertrand uses SQL Server metadata queries to discover SQL Server views that have certain properties or use certain syntax.
2014-12-12
5,919 reads
I'm using SQL Server's built-in encryption to hide data in one of my SQL Server databases, but this is a reporting system and my end users need to be able to query the data without having to remember the specialized decryption functions. Is there a way to do this? Yes, there is, via the use of views.
2013-10-29
4,794 reads
I need to create views? How many? Today? This article talks about a few issues with using views too extensively in your design.
2012-10-02
7,746 reads
Be sure to understand the consequences of modifying underlying tables and the affects they can have on Views
2012-07-10
5,339 reads
