Post
Ask Question
Aradhana Tripathi

Aradhana Tripathi

  • 448
  • 3k
  • 100.1k

Getting list of Stored Procedures referring specific table name

Mar 31 2023 1:30 PM

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

Answers (5)