SQL Server Portal

In my earlier article, I explained the importance of sp_refreshview and its implementation with examples. Recently I was working on a project where I used sp_refreshview quite frequently in view of the bulk customization in the database structure. But I needed to customize the standard script to refresh multiple views simultaneously due to the scenarios given below:

1. If one table has been modified, then all the views related to that particular table should be refreshed.
2. If multiple tables have been modified, then all views related to those tables should be refreshed.

Let me demonstrate the solution for both issues.

1. If one table has been modified, then all the views related to that particular table should be refreshed.

When I came across this scenario, I thought of finding all views related to the modified table and refresh it one by one but it is obviously time consuming. So alternatively I designed the given below script to accommodate this scenario.

--This script is compatible with SQL Server 2005 and above.
USE tempdb --Change the database name
GO
SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''  As [Text]
FROM sys.objects AS A
INNER JOIN sys.sql_expression_dependencies AS B
ON A.object_id = B.referencing_id
WHERE A.type = 'V'
AND B.referenced_id = OBJECT_ID('tbl_A') --Change the table name
GO

Once you have the above result set, just execute it in a new query window then all the views related to the above table would be refreshed.

2. If multiple tables have been modified, then all views related to those tables should be refreshed.

This is also very common scenario when you modify multiple tables and you need to refresh all views related to these tables. The standard script of standard script cannot accommodate this scenario, so I designed another script that fits this scenario. You just need to pass the table modification date and it picks all the views related to those tables and refreshes it. Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb --Change the database name
GO
DECLARE @Date AS DATETIME
SET @Date='2014-06-15'
--Change the table modification date

SELECT 'EXEC sp_refreshview ''' + name + ''''  As [Text]
FROM sys.objects AS A
INNER JOIN sys.sql_expression_dependencies AS B
ON A.object_id = B.referencing_id
WHERE A.type = 'V'
AND B.referenced_id IN (
SELECT object_id FROM sys.tables
WHERE CONVERT(varchar(11),modify_date)=@Date)
GO

Once you have the above result set, just execute it in a new query window then all the views related to the modified tables would be refreshed.

Let me know if you come across any scenario like this and its solution.