主页 / dba / 问题 / 250566
Accepted
SQLMIKE
Asked: 2019-10-09 08:29:42 +0800 CST

SQL SERVER：查找并删除无效视图

  • 772

我有一个 SQL Server 2012 数据库，其中包含许多无效的视图。视图引用的基础表已迁移到另一个模式以进行归档。找到现在无效的视图并删除它们的最佳方法是什么

view sql-server-2012

1 个回答

  1. Best Answer

    您可以使用 sp_refreshview 来完成它。我将这个小脚本放在一起，它将选择数据库中的所有视图，对其运行 sp_refreshview 并告诉您哪些有问题。

    https://learn.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
    • 3

相关问题