SQL SERVER：查找並刪除無效視圖

我有一個 SQL Server 2012 數據庫，其中包含許多無效的視圖。視圖引用的基礎表已遷移到另一個模式以進行歸檔。找到現在無效的視圖並刪除它們的最佳方法是什麼

您可以使用 sp_refreshview 來執行此操作。我把這個小腳本放在一起，它將選擇數據庫中的所有視圖，對它執行 sp_refreshview 並告訴你哪些有問題。

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

引用自：https://dba.stackexchange.com/questions/250566