How do I identify views with broken dependencies in SQL Server?

Question

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?

Answer 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

Updated the answer as per @robyaw's answer.

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.

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

Answer 2

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):

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;

Answer 3

Try this

Call sp_refreshsqlmodule on all non-schema bound stored procedures:

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.

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.

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

Answer 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

Answer 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.