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
Thanks for all your help.
your welcome Frankie!
Hi Pinal, Your options are pulling extra records where that table is NOT present. E.g. I was looking for Table “Device” and also we have Column “DeviceID” in other table. Your options were pulling records for DeviceID as well. I found below code more reliable. But you are the best judge.
select distinct [Table Name] = o.Name, [Found In] = sp.Name, sp.type_desc
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’, ‘FN’)
where o.name = ‘Device’
order by sp.Name
Your solution is using catalog view and would be better to use when dependencies in catalog view is correctly populated. I am querying raw text of code using syscomments.
Your solutions works, thank you so much, but i also have a requirement to pass multiple tables as combination and retrieve the Stored Procedures. Thanks in Advance!
thank u so much , now is was working good
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