Click here to Skip to main content
Click here to Skip to main content

MS SQL Server - SQL Object Dependency Check

, 4 Dec 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
Check if dependency of a table are objects referenced by a stored proc. A system table with CTE can help to identify dependency quickly.

Use the following SP:

CREATE PROC [dbo].GetDependentObjectList @ObjectName AS sysname 
AS
 
WITH ObjectDepends(entity_name,referenced_database_name,referenced_schema, 
                   referenced_entity, referenced_id,level)
AS(    
SELECT entity_name = CASE referencing_class                              
                       WHEN 1 THEN OBJECT_NAME(referencing_id)
                       WHEN 12 THEN (SELECT t.name 
                                     FROM sys.triggers AS t                                            
                                     WHERE t.object_id = sed.referencing_id)                              
                       WHEN 13 THEN (SELECT st.name 
                                     FROM sys.server_triggers AS st                                           
                                     WHERE st.object_id = sed.referencing_id) COLLATE database_default 
                     END,
       referenced_database_name,
       referenced_schema_name,
       referenced_entity_name,
       referenced_id,
       0 AS level     
 FROM SYS.SQL_Expression_Dependencies AS sed     
WHERE OBJECT_NAME(referencing_id) = @ObjectName 
 
UNION ALL    
 
SELECT entity_name = CASE sed.referencing_class                              
                       WHEN 1 THEN OBJECT_NAME(sed.referencing_id)                              
                       WHEN 12 THEN (SELECT t.name FROM sys.triggers AS t                                            
                                     WHERE t.object_id = sed.referencing_id)                              
                       WHEN 13 THEN (SELECT st.name 
                                     FROM sys.server_triggers AS st
                                     WHERE st.object_id = sed.referencing_id) COLLATE database_default 
                     END,
        sed.referenced_database_name,
        sed.referenced_schema_name,
        sed.referenced_entity_name,
        sed.referenced_id,level + 1
 FROM ObjectDepends AS o    
 JOIN SYS.SQL_Expression_Dependencies AS sed 
      ON sed.referencing_id = o.referenced_id) 
 
SELECT entity_name AS referencing_entity, referenced_database_name,
       referenced_schema,referenced_entity, level  
  FROM ObjectDepends 
 ORDER BY level;

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

GuruprasadV
Technical Lead Microsoft
India India
f

Comments and Discussions

 
GeneralIt only works with SQL Server 2008 onwards. Pinmemberbetoappdev6-Dec-11 7:36 
GeneralDoes this work for sql server 2005? Pinmembertpattani6-Dec-11 4:28 
GeneralRe: It only works with SQL Server 2008 onwards. Pinmemberbetoappdev6-Dec-11 7:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150326.1 | Last Updated 4 Dec 2011
Article Copyright 2011 by GuruprasadV
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid