Show all SPs that referencing a table | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Show all SPs that referencing a table

Hello, how can I show all the SPs that referencing a particular table? These include Update, Delete, Insert and Select (including in the Join). Thanks!
Hello, What you can do is query the syscomments table like : select
so.name
from
syscomments sc, sysobjects so
where
sc.text like ‘%table_name_here%’ and
sc.id=so.id and
so.xtype=’P’ regards,
Faruk
Although not 100% reliable, you can also query the sysdepends table. The easiest way to do it is with the sproc sp_depends. This is documented in BOL if you need more detail. Chris

I echo Chirs’s comments and advise not to mingle with system tables columns when you’ve SPs to get the results. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Yes, sp_depends and the SysDepends are not reliable. I know 1 table that have at least 20 SPs referencing it, but the sp_depends only show 3. Quite a big gap.
By the way, we are always using fbatakci’s suggestion. But, sometimes the results return more than what it should be becuase of the comment contains the table name, etc. So I thought maybe there is a better way to do it.
sp_depends will return the correct results if you make sure that the stored procedures have been compiled after the tables have been created. To get it to return 100% correct answers on which procedures call which other procedures, then you need to ensure that you create the procedures in the correct order (so that you don’t get any object not found type messages when creating the procs) I do also use the select on syscomments, remember that this cuts a procedure up into bite size varchar fields. These may cut a table name in half… Cheers
Twan
]]>