如何在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?

识别缺少依赖关系的视图的最佳方法是什么？

5 个解决方案

#1

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

Update 2017

2017年更新

Updated the answer as per @robyaw's answer.

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

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.

我还修复了select语句中计算值的错误。 vwTest中的SELECT TOP 1 NULL似乎没有抛出错误，当vwTest包含一个列，比如让我们说1/0为[Col1]，但是从vwTest中选择SELECT TOP 1 *会抛出异常。

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

更新2018修复名称中包含特殊字符的视图和/或模式的误报。感谢@LucasAyala

#2

Try this

尝试这个

Call sp_refreshsqlmodule on all non-schema bound stored procedures:

在所有非架构绑定存储过程上调用sp_refreshsqlmodule：

DECLARE @template AS varchar(max) 
SET @template = 'PRINT ''{OBJECT_NAME}'' 
EXEC sp_refreshsqlmodule ''{OBJECT_NAME}'' 

' 

DECLARE @sql AS varchar(max) 

SELECT  @sql = ISNULL(@sql, '') + REPLACE(@template, '{OBJECT_NAME}', 
                                          QUOTENAME(ROUTINE_SCHEMA) + '.' 
                                          + QUOTENAME(ROUTINE_NAME)) 
FROM    INFORMATION_SCHEMA.ROUTINES 
WHERE   OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.' 
                                 + QUOTENAME(ROUTINE_NAME)), 
                       N'IsSchemaBound') IS NULL 
        OR OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.' 
                                    + QUOTENAME(ROUTINE_NAME)), 
                          N'IsSchemaBound') = 0 

        EXEC ( 
              @sql 
            ) 

This works for all views, functions and SPs. Schemabound objects won't have problems and this can't be run on them, that's why they are excluded.

这适用于所有视图，功能和SP。 Schemabound对象不会出现问题而且无法对它们运行，这就是它们被排除的原因。

Note that it is still possible for SPs to fail at runtime due to missing tables - this is equivalent to attempting to ALTER the procedure.

请注意，由于缺少表，SP仍可能在运行时失败 - 这相当于尝试更改过程。

Note also that just like ALTER, it will lose extended properties on UDFs - I script these off and restore them afterwards.

还要注意，就像ALTER一样，它将丢失UDF上的扩展属性 - 我将其编写为脚本并在之后恢复它们。

#3

Adrian Iftode's solution is good, but fails if there are views that are not associated with the default schema. The following is a revised version of his solution that takes schema into account, whilst also providing error information against each failing view (tested on SQL Server 2012):

Adrian Iftode的解决方案很好，但如果有与默认架构无关的视图则会失败。以下是他的解决方案的修订版本，它考虑了架构，同时还针对每个失败的视图提供错误信息（在SQL Server 2012上测试）：

DECLARE @stmt       NVARCHAR(MAX) = '';
DECLARE @vw_schema  NVARCHAR(255);
DECLARE @vw_name    NVARCHAR(255);

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

CREATE TABLE #nullData
(  
    null_data VARCHAR(1)
);

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

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

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @stmt = CONCAT(N'SELECT TOP 1 NULL FROM ', @vw_schema, N'.', @vw_name);

    BEGIN TRY
      -- silently execute the "select from view" query
        INSERT INTO #nullData EXECUTE sp_executesql @stmt;
    END TRY 
    BEGIN CATCH
        INSERT INTO #badViews ([schema], name, error)
        VALUES (@vw_schema, @vw_name, ERROR_MESSAGE());
    END CATCH

    FETCH NEXT FROM tbl_cursor INTO @vw_schema, @vw_name;
END

CLOSE tbl_cursor;
DEALLOCATE tbl_cursor;    

-- print the views with errors when executed
SELECT * FROM #badViews;

DROP TABLE #badViews;
DROP TABLE #nullData;

#4

If you're using SQL Server 2005 or 2008, you could import the project in to Visual Studio 2008 or 2010 and analyze broken dependencies from the Visual Studio project

如果您使用的是SQL Server 2005或2008，则可以将项目导入Visual Studio 2008或2010并从Visual Studio项目中分析损坏的依赖项

#5

I would backup the database, restore it on my dev machine, create a script with all the views in a new window in management server, drop all views and try executing the script. Whenever a view is "corrupt", the execution will fail with an error message, e.g. Not existing table or column.

我将备份数据库，在我的开发机器上恢复它，在管理服务器的新窗口中创建包含所有视图的脚本，删除所有视图并尝试执行脚本。每当视图“损坏”时，执行将失败并显示错误消息，例如不存在表或列。