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
I found one situation where the information is not returned that is if you have a union all in the stored procedure. This is because (apparently) the second table/view is not considered in the dependency list. I assume this is because the driving data is the first table/view. Not sure if there is a way around this.
Nice Query.. Thank You.
Hi, Can you help me, how to find “inner Join or Cross Join” contained SPs in a Data base
srikanth:
Use the same code as in this thread except change the search criteria from a tablename to cross join and then inner join. However inner joins may be coded with just join so you may miss some. If you are looking for specific joins you are better off looking for the table name instead of or in addition to the join.
Hope that helps,
DG
THANKS FOR WONDERFUL ANSWER
Hi Mshah,
You can find dependant tables using sp_depends ‘TableName’
Regards,
Girijesh
Hi i want to get table name and column names from particular stored procedure.
eg: sp1 – used what table and columns in single select query
Thanku..very much…
how to find Vice versa of this
how to find all the tables for a single stored procedure
Thanks Pinal. Great article!
Hi Pinal,
This is the first time i am writing to this thread,
I just need a small help from you.
I need to get the list of tables used in a SQL Job, which may contains DTS Packages(So consider the tables which are been used in DTS too).
Thanks in Advance.
Abhinav
I don’t think that dependency is store in any table.
what is the error?
thanks boss
The error I found is where a stored procedure is split across multiple rows in the SYSCOMMENTS table and the table we’re trying to find references to is where the split between SYSCOMMENTS rows is. There should be a way to concatenate the columns together where they relate to the same stored procedure.
Thanks for all your help.
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 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