Recreate All Views in a Database with a SQL Query

6/26/2017 6:23:50 PM

The following will loop through every View in the database and recreate each view. This is important if you make a change to an underlying table.

SET NOCOUNT ON

DECLARE @ActualView varchar(255)

DECLARE viewlist CURSOR FAST_FORWARD
FOR
SELECT
	DISTINCT s.name + '.' + o.name AS ViewName
FROM sys.objects o JOIN sys.schemas s ON o.schema_id = s.schema_id 
WHERE	o.[type] = 'V'
		AND OBJECTPROPERTY(o.[object_id], 'IsSchemaBound') <> 1
		AND OBJECTPROPERTY(o.[object_id], 'IsMsShipped') <> 1

OPEN viewlist

FETCH NEXT FROM viewlist 
INTO @ActualView

WHILE @@FETCH_STATUS = 0
BEGIN

	PRINT @ActualView
	EXEC sp_refreshview @ActualView
	
	FETCH NEXT FROM viewlist
	INTO @ActualView
	
END

CLOSE viewlist
DEALLOCATE viewlist