MSSQL find objects with missing dependencies

You can use the below query snippet to list objects in a database that use dependencies which are missing. For example stored procedures that require a certain table/view which doesn’t exist (anymore).

This script can help you to clean up old/deprecated/obsolete database objects.

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