List all stored procedures that contain a specified table in T-SQL

Hi Experts,
Working with a SQL Server 2008 R2 database that has hundreds of Stored Procedures (sprocs).

An employee built a table called mh_client. mh_client needs to be replaced as it is causing issues.

I would like to run a query that can list all the sprocs that contain mh_client so I can discuss the ramifications of these changes with the rest of the team.

Any advice?

John
JohnAerisAsked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor Commented:
try this

select dISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%mh_client%'
0
 
Pratima PharandeConnect With a Mentor Commented:
above will return all sp's views and functions

for only stopre procedures try this

select distinct so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%mh_client%'
and xtype='P'
0
 
JohnAerisAuthor Commented:
Thank you!
Actually, the table appeared in views as well, which was not anticipated, so your first code was the most useful!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.