Store Your Code Now
Tools: String Manipulations Find Dupes My IP Json Formatter Lorem Ipsum More...
Discussion Options

Recreate All Views in a Database with a SQL Query

6/26/2017 2:23:50 PM by: CodeLocker

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.

View Copy: Content Copied
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
Copy: Content Copied
×

Get Coding

Store your first code or add more code to your collection.
You must login to report abuse. ×