Jon Milliken Jon Milliken - 10 months ago 59
SQL Question

Query to list which tables are used in stored procedures

I'm looking for a way to see all what stored procedures impact specific tables. Is there a better way than using


select * from DatabaseName.information_schema.routines
where routine_type = 'PROCEDURE'
and ROUTINE_DEFINITION like '%TableName%'

source: Query to list all stored procedures

Answer Source

This query will give you all the Stored Procedures that depend on table 'MyTable':

SELECT as [ObjectName], as [DependsOn]  
  FROM sys.sql_expression_dependencies d
       INNER JOIN sys.objects s ON d.referencing_id = s.object_id
       INNER JOIN sys.objects t ON d.referenced_id = t.object_id
 WHERE = 'MyTable' AND s.type = 'P'

You can alter the WHERE clause to be more specific, or less specific, or also add in functions, views, etc. if you wish.