SQL Server why must I refresh views after table changes

Question

This question came out of a previous post/solution of mine:

adding a field somehow effects a views results

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

Why do I have to refresh views after adding a column to a table which is referenced in the view? Although not directly necessary to answer this post/question, the scenario/behavior for my specific scenario is explained the the post linked above.

I am not a big fan of views. Its very rare I create them to be honest, I am working with code that I did not originally write. Let's say you have 70+ views which you didn't originally write therefore have no idea which ones need refreshing every time you add a data base column. I should be able to add what ever column what ever table anytime without any impact frankly. A business unit can raise a request for any kind of change that may required any number of fields added anytime.

Surely there is another approach to this?

Answer 1

Use the dynamic management views to identify which views were impacted by your table change, then loop through the results of the impacted 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 could adapt to a stored proc 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