Post-Deployment - Scan for RefreshView/Recompile

,

I ran into an issue recently where a table was changed (in beta) and subsequently a view that relied on it was rendered unusable. The idea for this script stemmed from that - to create a script to quickly identify and refresh any view that depends on a changed table. There's room to grow as I realize this isn't a deep dive nor does it cover things like nested views, table functions, etc. but it's a good first pass CYA and I'd welcome any comments or suggestions.

Full disclosure: the dev who made the initial table change also made a note in our problem tracking system that the view should be dropped but failed to include a script to do so. That problem as also been mitigated.

USE tempdb;
GO

DECLARE@l_CutOffDate VARCHAR(10) = CAST(CONVERT(DATE,GETDATE()) AS VARCHAR(10))
,@l_SQL NVARCHAR(MAX);

DECLARE @SQL_CMDS TABLE (ObjName NVARCHAR(128), SQL_CMDS NVARCHAR(MAX));

SET @l_SQL = N'
USE [?];
IF DB_NAME() NOT IN (''master'', ''msdb'', ''model'', ''tempdb'', ''ReportServer'', ''ReportServerTempDB'')
BEGIN
WITH MODIFIED_TABLES AS (
SELECTo.object_id
,OBJECT_SCHEMA_NAME(o.object_id) + ''.'' + o.name AS tblname
FROMsys.objects o
WHEREo.modify_date >= ' + '''' + @l_CutOffDate + '''' + '
ANDo.type = ''U'' -- Table (user-defined)
)
, MODIFIED_VIEWS AS (
SELECTo.object_id
,OBJECT_SCHEMA_NAME(o.object_id) + ''.'' + o.name AS viewname
,1 AS RecompileView
FROMsys.objects o
WHEREo.modify_date >= ' + '''' + @l_CutOffDate + '''' + '
ANDo.type = ''V'' -- View
UNION
SELECTv.object_id
,OBJECT_SCHEMA_NAME(v.object_id) + ''.'' + v.name AS viewname
,0 AS RecompileView
FROMMODIFIED_TABLES t
INNER JOIN sys.sql_dependencies d ON d.object_id = t.object_id
INNER JOIN sys.views v ON v.object_id = d.referenced_major_id
)

SELECTMODIFIED_TABLES.tblname
,''USE '' + DB_NAME() + ''; EXEC sp_recompile '' + '''' + DB_NAME() + ''.'' + MODIFIED_TABLES.tblname + '''' + '';''
FROMMODIFIED_TABLES
UNION
SELECTMODIFIED_VIEWS.viewname
,''USE '' + DB_NAME() + ''; EXEC sp_refreshview '' + '''' + DB_NAME() + ''.'' + MODIFIED_VIEWS.viewname + '''' + '';''
+ CASE WHEN MODIFIED_VIEWS.RecompileView = 1 THEN '' EXEC sp_recompile '' + '''' + DB_NAME() + ''.'' + MODIFIED_VIEWS.viewname + '''' + '';''
ELSE ''''
END
FROMMODIFIED_VIEWS;
END;
'

INSERT INTO @SQL_CMDS (ObjName, SQL_CMDS)
EXEC sys.sp_MSforeachdb @command1 = @l_SQL;

SELECTObjName
       ,SQL_CMDS
FROM@SQL_CMDS;

Rate

Share

Share

Rate