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

You may be interested in checking out the following articles:

• Michael J. Swart: Find Missing SQL Dependencies
• eggheadcafe.com: Find broken stuff

You can test Michael J. Swart's solution as follows:

CREATE PROCEDURE proc_bad AS     SELECT col FROM nonexisting_table GO  SELECT     OBJECT_NAME(referencing_id) AS [this sproc or VIEW...],     referenced_entity_name AS [... depends ON this missing entity name] FROM      sys.sql_expression_dependencies WHERE      is_ambiguous = 0     AND OBJECT_ID(referenced_entity_name) IS NULL ORDER BY      OBJECT_NAME(referencing_id), referenced_entity_name; 

Which returns:

+------------------------+------------------------------------------+ | this sproc or VIEW...  |  ... depends ON this missing entity name | |------------------------+------------------------------------------| | proc_bad               |  nonexisting_table                       | +------------------------+------------------------------------------+ 

Answer 2

The two previous solutions here are interesting, but both failed on my test databases.

The original Michael J Swart script produced a huge number of false positives for me, far too many to wade through. Rick V.'s solution here was better - the only false positives it gave were for cross-database references.

There's a comment on the Michael J Swart article by RaduSun which gives a solution that I can't yet break though! This is it, tweaked mildly for readability and my purposes, but credit to RaduSun for the logic.

SELECT      QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.'          + QuoteName(OBJECT_NAME(referencing_id)) AS ProblemObject,     o.type_desc,     ISNULL(QuoteName(referenced_server_name) + '.', '')     + ISNULL(QuoteName(referenced_database_name) + '.', '')     + ISNULL(QuoteName(referenced_schema_name) + '.', '')     + QuoteName(referenced_entity_name) AS MissingReferencedObject FROM     sys.sql_expression_dependencies 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) ORDER BY     ProblemObject,     MissingReferencedObject