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

Your best bet is to start using a tool like Visual Studio Database Edition. It's role is to manage a database schema. One of the many things it will do is to throw an error when you attempt to build the database project and it contains broken objects. It will of course do much more than this. The tool is free to any user of Visual Studio Team Suite or Visual Studio Developer Edition.