Getting list of Stored Procedures referring specific table name

Aradhana Tripathi

Hello All,

I was trying to get all SP's referring ABS_tbl using below query. but in output it retieve all the SP's referring ABS_tbl including those having incorrect suffix.

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE so.type in ('P') and TEXT like '%ABC_tbl%'
--Output- 250+ SPs

Also tried below query by removing % as suffix but it returns only 1 SP and filtering even those that referred table name as ABC_tbl. Am I doing anything wrong here?

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE so.type in ('P') and TEXT like '%ABC_tbl'
--output 1 SP

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty3y ago

    SELECT DISTINCT so.name
      FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
     WHERE sc.text LIKE '%YourTableName%' AND so.type='P'
  ORDER BY so.name

Anupam Maiti3y ago

Can you try this and let me know if it works for you.

SELECT DISTINCT o.name
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE o.type = 'P'
AND m.definition LIKE '%ABC_tbl%'
AND m.definition NOT LIKE '%[_a-z]ABC_tbl%' ESCAPE '_'

Sachin Singh3y ago

Test this

select distinct sp.Name
from sys.objects o inner join sys.sql_expression_dependencies  sd 
on o.object_id = sd.referenced_id
inner join sys.objects sp on sd.referencing_id = sp.object_id
and sp.type in ('P')
where o.name = 'ABC_tbl'  --your table name
order by sp.Name

Naimish Makwana3y ago
Try below conditoin.
```
'%ABS_tbl%' OR '%dbo.ABS_tbl%' OR '%ABC_tbl_alias%'
```
Thanks
0

Rajkiran Swain3y ago

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE so.type in ('P') and TEXT like '%ABC_tbl%'