SQL Server, why should I update the views after changing the table

Question

This question came from a previous post / my solution:

Adding a field somehow affects view results

IMO deserves its own post. Using SQL Server 2008 R2.

Why do I need to update views after adding a column to the table referenced in the view? Although this is not necessary to answer this post / question, the script / behavior for my particular scenario explain the message above.

I am not a big fan of looks. Very rarely, I create them to be honest, I work with code that I did not originally write. Let's say you have 70 views that you did not initially write, so you have no idea which ones need to be updated each time you add a database column. I should be able to add that ever column that there is ever a table at any time, without any influence frankly. A business unit may raise a request for any changes that may require any number of added fields at any time.

Is there really another approach to this?

Answer 1

Use dynamic management views to determine which views were affected by your table change, then swipe through the results of remote views to use dynamic sql to call sp_refreshview ( https://msdn.microsoft.com/en-us/library/ms187821(v = sql.105) .aspx )

Here's a quick script, you can adapt to the saved process if you want:

 DECLARE @TableName VARCHAR(500) = 'dbo.Accounts' DECLARE @ViewsToUpdate TABLE (ViewName VARCHAR(500)) INSERT @ViewsToUpdate SELECT Views.TABLE_SCHEMA + '.' + Views.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES [Views] INNER JOIN sys.dm_sql_referencing_entities(@TableName, 'OBJECT') DependingViews ON DependingViews.referencing_schema_name = Views.TABLE_SCHEMA AND DependingViews.referencing_entity_name = Views.TABLE_NAME WHERE [Views].TABLE_TYPE = 'View' WHILE EXISTS (SELECT * FROM @ViewsToUpdate) BEGIN DECLARE @ViewName VARCHAR(500) = (SELECT TOP 1 ViewName FROM @ViewsToUpdate) DECLARE @Sql NVARCHAR(1000) = 'EXEC sp_refreshview ''' + @ViewName + '''' EXEC sys.sp_executesql @Sql DELETE @ViewsToUpdate WHERE ViewName = @ViewName END