MSSQL List of Table's Referencing Objects

Similarly as we did in our Oracle past post, we now propose a simple but useful MSSQL query, that let us to know which objects (for example, stored procedures) reference a specified table (or any other object):


SELECT DISTINCT
   referenced_schema = d.referenced_schema_name
   , referenced_object_name = d.referenced_entity_name
   , referenced_object_type = o1.type_desc
   , referring_object_schema = s.name
   , referring_object_name = o.name
   , referring_object_type = o.type_desc
FROM
   sys.sql_expression_dependencies d
   INNER JOIN sys.objects o ON d.referencing_id=o.object_id
   INNER JOIN sys.schemas s ON o.schema_id=s.schema_id
   INNER JOIN sys.objects o1 ON d.referenced_id=o1.object_id
WHERE
   --d.referenced_entity_name = 'table_name'
   d.referenced_entity_name like '%object_name%'
ORDER BY
       referenced_schema_name
;



But how can MSSQL obtain such a list of referenced table objects, contained inside the code of a stored procedure? Well, everytime you CREATE or ALTER a stored procedure, the compiler dynamically creates a list of syntatic objects, internally implemented as hash table; this list contains also all the code's referenced table, and it´s therefore used to keep the system tables and metadata updated.