0

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

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

source: Query to list all stored procedures

Community
  • 1
  • 1
Jon Milliken
  • 121
  • 11
  • Try `sp_depends tablename` – techspider Oct 11 '16 at 17:50
  • Hi Jon, Verify the link for reference http://stackoverflow.com/questions/12622920/list-all-stored-procedures-with-schema-name – Jim Macaulay Oct 11 '16 at 17:52
  • 1. SP_Depends is not providing the information needed, and doing a quick search on SP_Depends, the general consensus is that it is out of date and not accurate. – Jon Milliken Oct 11 '16 at 18:02
  • 2. Yes, that is the correct link. I am able to get the information needed from the query above, but i don't like querying using "LIKE" in this manner. It isn't good form. – Jon Milliken Oct 11 '16 at 18:04

3 Answers3

0

I think these mgmt objects were added in SQL Server 2012 but these statements are what I use to find dependencies:

SELECT
OBJECT_SCHEMA_NAME(referencing_id) AS srcschema,
OBJECT_NAME(referencing_id) AS srcname,
referencing_minor_id AS srcminorid,
referenced_schema_name AS tgtschema,
referenced_entity_name AS tgtname,
referenced_minor_id AS tgtminorid
FROM sys.sql_expression_dependencies;

SELECT
referenced_schema_name AS objschema,
referenced_entity_name AS objname,
referenced_minor_name AS minorname,
referenced_class_desc AS class
FROM sys.dm_sql_referenced_entities('dbo.ProcName', 'OBJECT');

SELECT
referencing_schema_name AS objschema,
referencing_entity_name AS objname,
referencing_class_desc AS class
FROM sys.dm_sql_referencing_entities('dbo.TableName', 'OBJECT');
Nate H
  • 76
  • 6
0

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

SELECT s.name as [ObjectName], t.name 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 t.name = '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.

pmbAustin
  • 3,890
  • 1
  • 22
  • 33
0

You could join the tables meta data to the stored procedure meta data as such:

SELECT sp.SPECIFIC_NAME
    ,t.TABLE_NAME
FROM information_schema.routines sp
INNER JOIN INFORMATION_SCHEMA.TABLES t 
    ON sp.ROUTINE_DEFINITION LIKE '%' + t.TABLE_NAME + '%'
WHERE sp.routine_type = 'PROCEDURE'
ORDER BY t.TABLE_NAME
Gary
  • 199
  • 5
  • 12