Michael J. Swart

December 17, 2009

Find Missing SQL Dependencies

Filed under: SQL Scripts,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 12:01 pm

A short post today.

I use the following script to find (broken) stored procedures or views that refer to sprocs or tables that no longer exist… or that don’t exist yet.

This is a quick sanity check that can help identify broken or obsolete stored procedures.

-- 2008 only
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
    AND referenced_entity_name NOT IN
        (SELECT Name FROM sys.types WHERE is_user_defined = 1)
ORDER BY OBJECT_NAME(referencing_id), referenced_entity_name

14 Comments »

  1. Howabout something that rebuilds all dependencies when an update occurs. In other words… if I want to change a view… I want to automatically rebuild all stored procedures and dependent views that are associated to this? ( mainly because I hate that sql server loses its dependency tracking if you commit something out of order )

    I NEED A REBUILD DEPENDENCY LIST! ( and I don’t want to buy another app to do it for me )

    Comment by Richard Wallace — December 18, 2009 @ 2:18 pm

  2. Well,
    In this script I check for OBJECT_ID(referenced_entity_name) rather than referenced_id which kind of avoids that whole issue.

    In the meantime, in 2008, You might be able to do something by combining
    sp_refreshsqlmodule with the results of sys.dm_sql_referencing_entities.

    Aaron Bertrand has the definitive blog post about this (which you may have already read):
    http://sqlblog.com/blogs/aaron_bertrand/archive/2008/09/09/keeping-sysdepends-up-to-date-in-sql-server-2008.aspx

    Comment by Michael J. Swart — December 18, 2009 @ 2:56 pm

  3. […] Find Missing SQL Dependencies If a view, function or stored procedure refers to a nonexistent object (be it a table, view etc…) This query can help you identify those. […]

    Pingback by Top 8 Queries to Improve DB Design | Michael J. Swart — December 21, 2009 @ 2:24 pm

  4. […] Find Missing SQL Dependencies – Nifty little script here, courtesy of Michale J Swart, for assisting with Finding Missing SQL Dependencies. […]

    Pingback by SQL Server Links (Xmas Edition) | John Sansom - SQL Server DBA in the UK — December 25, 2009 @ 5:38 am

  5. I’ve just extended the previous script to be aware of databases and servers and also to take care of non-standard names:


    SELECT TOP (100) PERCENT
    QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' + QuoteName(OBJECT_NAME(referencing_id)) AS [this sproc or VIEW...],
    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]
    FROM sys.sql_expression_dependencies
    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 [this sproc or VIEW...],
    [... depends ON this missing entity name]

    Comment by RaduSun — March 25, 2010 @ 9:26 am

  6. […] sott’occhio gli elementi non validi presenti nel database. Ho trovato la risposta nel blog di Michael J. Swart e in un thread sul […]

    Pingback by Trovare oggetti non validi in SQL Server 2008 | HeDo's Blog — May 7, 2011 @ 7:12 am

  7. Chad Dokmanovich

    The above SQLs both incorrectly report Proc1 as having missing dependency {Alias} because it doesn’t recognize that {Alias} is anotehr name for a table that exists rather than n-existing table.

    CREATE SUB Proc1
    As

    UPDATE {Alias}
    SET {Col} = {Value}
    FROM {Realtable} {Alias}

    Are there any fixes?

    Comment by Chad — August 28, 2012 @ 11:09 am

  8. I noticed that myself. You have to mentally exclude Aliases. And I haven’t checked in a long time, but I don’t remember hearing about any improvements to SQL Server in this area.

    Comment by Michael J. Swart — August 28, 2012 @ 11:12 am

  9. The following tool that we developed does just that:

    http://nobhillsoft.com/Diana.aspx

    we’re giving it for free… no catches no gimmicks

    Comment by Jonathan Scion — December 4, 2013 @ 9:08 pm

  10. @Chad

    I have found that if you do a refresh of the SP that contains the UPDATE {alias} it stops showing as a false positive

    EXEC sys.sp_refreshsqlmodule ‘[dbo].[spName]’

    Comment by Nate VH — July 1, 2014 @ 11:41 am

  11. Magical query, appreciate it. I don’t think it reports invalid DB objects on account of missing columns referenced, does it?

    Comment by Ranjit — September 30, 2014 @ 6:40 am

  12. Tweaked the script above to include the object types as columns, and added a column that generates a call to sys.sp_refreshsqlmodule to help eliminate false positives.

    SELECT TOP (100) PERCENT
    QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + ‘.’ + QuoteName(OBJECT_NAME(referencing_id)) AS [this sproc or VIEW…],
    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 = ‘SQL_STORED_PROCEDURE’
    then ‘EXEC sys.sp_refreshModule ‘ + 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)
    ORDER BY [this sproc or VIEW…],
    [… depends ON this missing entity name]

    Comment by SQLMonger — July 20, 2015 @ 12:24 pm

  13. Too hasty… Updated to add quotes around the call to sp_refreshsqlmodule… and to correct the proc name in the generated code column.

    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)
    ORDER BY [this Object…],
    [… depends ON this missing entity name]

    Comment by SQLMonger — July 20, 2015 @ 12:41 pm

  14. One further improvement: User-define types were causing false positives. Updated to address that gap…

    create view dbo.MissingObjectReferences
    as 
    /*
    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]
    go
     
    select * from dbo.MissingObjectReferences

    Comment by SQLMonger — July 20, 2015 @ 2:39 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress