Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results.
----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'
----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'
Reference : Pinal Dave (http://www.SQLAuthority.com)
167 Comments. Leave new
use sp_depends
Hi Pinal, why sp_depends is not reliable for this as you mentioned.
I would like to know number of result set should return in specific stored procedure.
Is it possible to find total resultset (Datatable) returning from StoredProcedure?
This is fine. But the problem with the LIKE is that if I have a table named Employees and a table named EmployeesPhones, when I look for Employees I’m obtaining the SPs for both tables