Dependent Objects

How many time you need to scan thru a stored procedure to find what are all the tables it uses.  Specially when you are working on a legacy application, it becomes most frequent task.

To simplify the work you can use SYS.SQL_Expression_Dependencies system table.

You can use the following CTE to get deeper object references

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;

GO