SQL Server – How to Find and Drop Invalid View

sql-server-2012view

I have a SQL Server 2012 database which contains a number of views which have become invalid. The underlying tables the views referenced were migrated to another schema for archiving. What's the best way to find the now invalid views and drop them

Best Answer

You can use sp_refreshview to do it. I've put this little script together that will select all views in a database, run sp_refreshview against it and tell you which ones had a problem.

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-refreshview-transact-sql?view=sql-server-2017

DECLARE @Objects TABLE
    (
    OID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , FQN NVARCHAR(256) NOT NULL
    , IsIssue BIT NOT NULL DEFAULT (CONVERT(BIT, 0))
    )

DECLARE @SQLCommand NVARCHAR(4000)
DECLARE @VName NVARCHAR(256)
DECLARE @OID INT

INSERT INTO @Objects 
    (FQN)
SELECT QUOTENAME(S.name) + '.' + QUOTENAME(V.name)
FROM sys.views AS V
    INNER JOIN sys.schemas AS S ON S.Schema_id = V.schema_id 

DECLARE curLoop CURSOR LOCAL STATIC FORWARD_ONLY
FOR SELECT OID, FQN
    FROM @Objects AS O

OPEN curLoop

FETCH NEXT FROM curLoop
INTO @OID, @VName 

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @SQLCommand = N'EXEC sp_refreshview N' + QUOTENAME(@VName, '''')

    BEGIN TRY

        EXEC sp_executesql @SQLCommand 

    END TRY
    BEGIN CATCH

        UPDATE @Objects 
        SET IsIssue = 1 
        WHERE OID = @OID 

    END CATCH

    FETCH NEXT FROM curLoop
    INTO @OID, @VName 

END

CLOSE curLoop
DEALLOCATE curLoop 

SELECT OID, FQN, IsIssue 
FROM @Objects 
WHERE IsIssue = 1
Related Question