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)
pinaldave
Hi Pinal,
I would the get all tables referenced in a store procedure.
Thank you,
CJ_Aexp
Hi, Thanks for helping me
i have problem to find explain plan for stored prcedure plz give me solution for this…
HI,
Its a nice querry
Hi,
Thanks a lot for helping me…
thx. this really works for me
I use bulit-in Extended SP sp_depends which returns same results. Eg. EXEC sp_depends #tablename#
hi i want a write a querry in stored procedure which can help me for taking the value from user and serch the table and give the result and i like it also in trigers like above criteria so plz help me
i am waiting for u
Hi Pinal,
Excellent Query. Thanks for sharing. Would you please help me how i can find out Dependable Tables/Views/Stored Procedures/ Constraints/Indexes/ForeignKeys constraint for given Table ?
Awesome! Thanks for this super useful query.
you are really a wonderfull person and good dba
thanks for helping us (beginners)
Best Regards
Chinmay
hi pinal
i have a query realted with how to use stored procedure from java to insering or deleting or updating the value in the table. and also want to knw how to call them
plz reply me soon.
Want to rename a table in a database.
Looking for a T-SQL script that will update all stored procedures and views in a database with the new table name.
Regards
Kevin
Hi Pinal
You might want to move this to another thread, but here’s an extended version of your example. I needed a way to search all objects in a database containing a certain string, in my case a reference to another database. This example makes it possible to find all references in triggers and stored procedures etc:
SELECT DISTINCT o.name AS ObjectName,
CASE o.xtype
WHEN ‘C’ THEN ‘CHECK constraint’
WHEN ‘D’ THEN ‘Default or DEFAULT constraint’
WHEN ‘F’ THEN ‘FOREIGN KEY constraint’
WHEN ‘FN’ THEN ‘Scalar function’
WHEN ‘IF’ THEN ‘In-lined table-function’
WHEN ‘K’ THEN ‘PRIMARY KEY or UNIQUE constraint’
WHEN ‘L’ THEN ‘Log’
WHEN ‘P’ THEN ‘Stored procedure’
WHEN ‘R’ THEN ‘Rule’
WHEN ‘RF’ THEN ‘Replication filter stored procedure’
WHEN ‘S’ THEN ‘System table’
WHEN ‘TF’ THEN ‘Table function’
WHEN ‘TR’ THEN ‘Trigger’
WHEN ‘U’ THEN ‘User table’
WHEN ‘V’ THEN ‘View’
WHEN ‘X’ THEN ‘Extended stored procedure’
ELSE o.xtype
END AS ObjectType,
ISNULL( p.Name, ‘[db]‘) AS Location
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
LEFT JOIN sysobjects p ON o.Parent_obj=p.id
WHERE c.text LIKE ‘%code/SQL command/any text to search for%’
ORDER BY Location, ObjectName
Note: this CANNOT be used to find information in tables - that’s something competely different.
really works great! thanks!
The query by Jens Johanneson was really helpful to find the references to functions.
Hi.
I need a code for searching inside a .doc file which is stored in sql server database using c#.net
nice code. thanks.
Hi!! its a very nice steps.
thanks,
[...] Reference : SQL SERVER - 2005 - Search Stored Procedure Code - Search Stored Procedure Text, SQL SERVER - Find Stored Procedure Related to Table in Database - Search in All Stored procedure [...]
Nice website, Pinal.
Using SSMS, You can also right click a table and select “View Dependencies” to see which objects depend on the table including stored procedures.
You always have exactly what I need.
Thanks!
good material
Nice article
Thanks!!!
how to create a procedure to rename all the tables in a data base..
i need to find a particular column used in diff stored proc in sql server 2005 database.how will i find all the stored proc names where i am using that particular column.
hi,
really works great! thanks!
Thanks, this really work for me.
Very informative. This query was very useful…
Thanks a lot
Hi Pinal,
This query is great!
Was wondering can it be taken one step further and be used to rename a table in the DB.
eg. change table ‘Blah’ to ‘BlahBlahBlah’
Where this would update throughout the DB. Then i won’t have to manually change every object.
Thanks
Great article, thanks! Just a quick extension of the example to tables referenced by all procedures and views follows
SET NOCOUNT ON
IF OBJECT_ID(’tempdb..#work’) IS NOT NULL
DROP TABLE #work
CREATE TABLE #work
( name sysname
,xtype char(2)
,tablename sysname )
– List objects dependent on tables
DECLARE cur_tables CURSOR
READ_ONLY
FOR
SELECT name
FROM sysobjects
WHERE xtype = ‘u’
AND left(name,2) ‘dt’
DECLARE @name sysname
OPEN cur_tables
FETCH NEXT FROM cur_tables INTO @name
WHILE (@@fetch_status -1)
BEGIN
IF (@@fetch_status -2)
BEGIN
INSERT INTO #work
SELECT DISTINCT o.name ,o.xtype, @name as tablename
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE ‘%’ + @name + ‘%’
END
FETCH NEXT FROM cur_tables INTO @name
END
CLOSE cur_tables
DEALLOCATE cur_tables
select *
from #work
order by name
The query from Jens Johanneson is very usefull to me, thank you Jens
hi..
that’s a great query.. i am searching for this..
u r really a great man..
Hi Pinal,
This is the first time i’m writing to u and lemme tell you that
you have done an incredible job.
Thanx for the website and yeah nice query man.
Can you please tell me, What will i do if i have to view th e constraints for a particular table.
Like I have add primary key constraint in a table.
Now if i want to check for that PK_Constraint what will be the query that I have to execute.
Please help.
Dear Pinal
The query for finding the dependencies of a table is based on the syscomments table and the like operator , so it is giving results if the dbobject is in comments section or in a part of a sentence, which is inaccurate .
Can you give me a query for this problem.
Thank You.
Dileep.