Find broken objects in SQL Server

Question

Is there a tool that will find all objects in SQL Server (functions, procs, views) that cannot possibly work because they refer to objects that don't exist?

Answer 1

/*
modified version of script from http://michaeljswart.com/2009/12/find-missing-sql-dependencies/
Added columns for object types & generated refresh module command...
filter out user-define types: http://stackoverflow.com/questions/2330521/find-broken-objects-in-sql-server

*/

SELECT TOP (100) PERCENT
    QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' + QuoteName(OBJECT_NAME(referencing_id)) AS [this Object...],
        o.type_desc,
    ISNULL(QuoteName(referenced_server_name) + '.', '')
    + ISNULL(QuoteName(referenced_database_name) + '.', '')
    + ISNULL(QuoteName(referenced_schema_name) + '.', '')
    + QuoteName(referenced_entity_name) AS [... depends ON this missing entity name]
    ,sed.referenced_class_desc
    ,case when o.type_desc in( 'SQL_STORED_PROCEDURE' ,'SQL_SCALAR_FUNCTION' ,'SQL_TRIGGER' ,'VIEW')
          then 'EXEC sys.sp_refreshsqlmodule ''' + QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' + QuoteName(OBJECT_NAME(referencing_id)) + ''';'
          else null
       end as [Refresh SQL Module command]
FROM sys.sql_expression_dependencies as sed
LEFT JOIN sys.objects o
            ON sed.referencing_id=o.object_id
WHERE (is_ambiguous = 0)
AND (OBJECT_ID(ISNULL(QuoteName(referenced_server_name) + '.', '')
    + ISNULL(QuoteName(referenced_database_name) + '.', '')
    + ISNULL(QuoteName(referenced_schema_name) + '.', '')
    + QuoteName(referenced_entity_name)) IS NULL)
AND NOT EXISTS
   (SELECT * 
    FROM sys.types 
    WHERE types.name = referenced_entity_name 
    AND types.schema_id = ISNULL(SCHEMA_ID(referenced_schema_name), SCHEMA_ID('dbo'))
   )
ORDER BY [this Object...],
[... depends ON this missing entity name]

Answer 2

 create table #BrokenObjects (Name nvarchar(500), Error nvarchar(max))
 select * into #objects from(
 select name from sys.views
 union select name from sys.procedures
 union select name from sys.tables
 )x
 declare @name nvarchar(500),@err nvarchar(max)
 while exists(select top 1 * from #objects)
 begin
 select top 1 @name = name from #objects
 begin try
 EXEC sys.sp_refreshsqlmodule @name
 end try
 begin catch
 select @err = ERROR_MESSAGE()
 insert into #BrokenObjects (name,error) values (@name,@err)
 end catch
 delete from #objects
 where name = @name
 end
 drop table #objects
 select * from #BrokenObjects
 where Error not like 'Could not find object % or you do not have permission.'

 drop table #BrokenObjects

Answer 3

First query will give you broken objects name includes Stored Procedure,View,Scalar function,DML trigger,Table-valued-function type

/*
/////////////
////ERROR////
/////////////
All error will be listed if object is broken
*/
DECLARE @AllObjectName TABLE (
    OrdinalNo INT IDENTITY
    ,ObjectName NVARCHAR(MAX)
    ,ObjectType NVARCHAR(MAX)
    ,ErrorMessage NVARCHAR(MAX)
    )

INSERT INTO @AllObjectName (
    ObjectName
    ,ObjectType
    )
SELECT '[' + SCHEMA_NAME(schema_id) + '].[' + NAME + ']' ObjectName
    ,CASE [TYPE]
        WHEN 'P'
            THEN 'Stored Procedure'
        WHEN 'V'
            THEN 'View'
        WHEN 'FN'
            THEN 'Scalar function'
        WHEN 'TR'
            THEN 'DML trigger'
        WHEN 'TF'
            THEN 'Table-valued-function'
        ELSE 'Unknown Type'
        END
FROM sys.objects
WHERE [TYPE] IN (
        'P'
        ,'V'
        ,'FN'
        ,'TR'
        ,'TF'
        )
ORDER BY NAME

DECLARE @i INT = 1
DECLARE @RowCount INT = (
        SELECT count(1)
        FROM @AllObjectName
        )
DECLARE @ObjectName VARCHAR(MAX)

WHILE @i <= @RowCount
BEGIN
    BEGIN TRY
        SET @ObjectName = (
                SELECT ObjectName
                FROM @AllObjectName
                WHERE OrdinalNo = @i
                )

        EXEC sys.sp_refreshsqlmodule @ObjectName
    END TRY

    BEGIN CATCH
        DECLARE @message VARCHAR(4000)
            ,@xstate INT;

        SELECT @message = ERROR_MESSAGE()
            ,@xstate = XACT_STATE();

        IF @xstate = - 1
            ROLLBACK;

        UPDATE @AllObjectName
        SET ErrorMessage = @message
        WHERE OrdinalNo = @i
    END CATCH

    SET @i = @i + 1
END

SELECT ObjectName
    ,ObjectType
    ,ErrorMessage
FROM @AllObjectName
WHERE ErrorMessage IS NOT NULL

And the below one search for unresolved references .. Generally which treated as warning, is still may cause error sometime

/*
/////////////
///Warning///
/////////////
Here all warning will come if object reference is not stated properly
*/
SELECT TOP (100) PERCENT QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' + QuoteName(OBJECT_NAME(referencing_id)) AS [this Object...]
    ,o.type_desc
    ,ISNULL(QuoteName(referenced_server_name) + '.', '') + ISNULL(QuoteName(referenced_database_name) + '.', '') + ISNULL(QuoteName(referenced_schema_name) + '.', '') + QuoteName(referenced_entity_name) AS [... depends ON this missing entity name]
    ,sed.referenced_class_desc
FROM sys.sql_expression_dependencies AS sed
LEFT JOIN sys.objects o ON sed.referencing_id = o.object_id
WHERE (is_ambiguous = 0)
    AND (OBJECT_ID(ISNULL(QuoteName(referenced_server_name) + '.', '') + ISNULL(QuoteName(referenced_database_name) + '.', '') + ISNULL(QuoteName(referenced_schema_name) + '.', '') + QuoteName(referenced_entity_name)) IS NULL)
    AND NOT EXISTS (
        SELECT *
        FROM sys.types
        WHERE types.NAME = referenced_entity_name
            AND types.schema_id = ISNULL(SCHEMA_ID(referenced_schema_name), SCHEMA_ID('dbo'))
        )
ORDER BY [this Object...]
    ,[... depends ON this missing entity name]

Thanks @SQLMonger .. for providing me the clue to make the First query which was my actual requirement

Answer 4

Red Gate Software's SQL Prompt 5 has a Find Invalid Objects feature that might be useful in this situation. The tool goes through the database finding objects that will give an error when executed, which sounds exactly what you want.

You can download a 14-day trial for free, so you can give it a try and see if it helps.

Paul Stephenson
SQL Prompt Project Manager
Red Gate Software

Answer 5

As of SQL Server 2008, a much simpler method is here:

 SELECT OBJECT_NAME(referencing_id) AS 'object making reference' ,
       referenced_class_desc ,
       referenced_schema_name ,
       referenced_entity_name AS 'object name referenced' ,
       (   SELECT object_id
           FROM   sys.objects
           WHERE  name = [referenced_entity_name]
       ) AS 'Object Found?'
FROM   sys.sql_expression_dependencies e
       LEFT JOIN sys.tables t ON e.referenced_entity_name = t.name;

As mentioned in the source article (Microsoft MSDN Article on Finding Missing Dependencies), "A 'NULL' value in the 'Object Found?' column indicates the object was not found in sys.objects."

Example output:

╔═══════════════════════════════════════════════╦═══════════════════════╦════════════════════════╦═══════════════════════════════════════╦═══════════════╗
║            object making reference            ║ referenced_class_desc ║ referenced_schema_name ║        object name referenced         ║ Object Found? ║
╠═══════════════════════════════════════════════╬═══════════════════════╬════════════════════════╬═══════════════════════════════════════╬═══════════════╣
║ usvConversationsWithoutServerNotices          ║ OBJECT_OR_COLUMN      ║ dbo                    ║ ConversationLinesWithID               ║ NULL          ║
║ usvFormattedConversationLines_WithSpeakerName ║ OBJECT_OR_COLUMN      ║ dbo                    ║ ConversationLinesWithID               ║ NULL          ║
║ usvFormattedConversationLines_WithSpeakerName ║ OBJECT_OR_COLUMN      ║ dbo                    ║ FormattedConversationLines_Cached     ║ NULL          ║
║ udpCheckForDuplicates                         ║ OBJECT_OR_COLUMN      ║ dbo                    ║ FormattedConversationLines_WithChatID ║ NULL          ║
║ usvFormattedConversationsCombined             ║ OBJECT_OR_COLUMN      ║ dbo                    ║ GROUP_CONCAT_D                        ║ 178099675     ║
║ usvSequenceCrossValidationSetStudents         ║ OBJECT_OR_COLUMN      ║ dbo                    ║ usvSequenceCrossValidationSet         ║ 1406628054    ║
╚═══════════════════════════════════════════════╩═══════════════════════╩════════════════════════╩═══════════════════════════════════════╩═══════════════╝