如何识别 SQL Server 中依赖项损坏的视图? [英] How do I identify views with broken dependencies in SQL Server?

问题描述

我们在继承的数据库中有大量视图，其中一些视图缺少依赖项(表甚至其他视图)?

We have a large number of views in an inherited database which some of them are missing dependencies (table or even other views)?

识别缺少依赖项的视图的最佳方法是什么?

What's the best way to identify the views which have missing dependencies?

推荐答案

DECLARE @stmt nvarchar(max) = ''
DECLARE @vw_schema  NVARCHAR(255)
DECLARE @vw_name varchar(255)

IF OBJECT_ID('tempdb..#badViews') IS NOT NULL DROP TABLE #badViews
IF OBJECT_ID('tempdb..#nulldata') IS NOT NULL DROP TABLE #nulldata

CREATE TABLE #badViews 
(    
    [schema]  NVARCHAR(255),
    name VARCHAR(255),
    error NVARCHAR(MAX) 
)

CREATE TABLE #nullData
(  
    null_data varchar(1)
)


DECLARE tbl_cursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
    FOR SELECT name, SCHEMA_NAME(schema_id) AS [schema]
        FROM sys.objects 
        WHERE type='v'

OPEN tbl_cursor
FETCH NEXT FROM tbl_cursor
INTO @vw_name, @vw_schema



WHILE @@FETCH_STATUS = 0
BEGIN
    SET @stmt = 'SELECT TOP 1 * FROM [' + @vw_schema + N'].[' + @vw_name + ']'
    BEGIN TRY
        INSERT INTO #nullData EXECUTE sp_executesql @stmt
    END TRY 

    BEGIN CATCH
        IF ERROR_NUMBER() != 213 BEGIN
            INSERT INTO #badViews (name, [schema], error) values (@vw_name, @vw_schema, ERROR_MESSAGE())     
        END
    END CATCH


    FETCH NEXT FROM tbl_cursor 
    INTO @vw_name, @vw_schema
END

CLOSE tbl_cursor -- free the memory
DEALLOCATE tbl_cursor

SELECT * FROM #badViews

DROP TABLE #badViews
DROP TABLE #nullData

2017 年更新

根据@robyaw 的回答更新了答案.

Updated the answer as per @robyaw's answer.

我还修复了 select 语句中计算值的错误.当 vwTest 包含一个列，例如 1/0 as [Col1] 时，似乎 SELECT TOP 1 NULL from vwTest 不会引发错误，但是 SELECT TOP 1 * from vwTest 它确实抛出异常.

I've also fixed a bug in it for the computed values in the select statements. It seems SELECT TOP 1 NULL from vwTest doesn't throw an error when vwTest contains a column like let's say 1/0 as [Col1], but SELECT TOP 1 * from vwTest it does throw an exception.

2018 年更新修复名称中包含特殊字符的视图和/或架构的误报.感谢@LucasAyala

Update 2018 Fix false positives for views and or schema that contain special characters in their name. Thanks to @LucasAyala

这篇关于如何识别 SQL Server 中依赖项损坏的视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！