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
Hi Pinal,
I would the get all tables referenced in a store procedure.
Thank you,
CJ_Aexp
Hello,
I want to inquire that is it possible to write an SQL query in which I enter the stored procedure name and it returns me the package
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#
sp_depends does not return the same results in my experience.
When I run sp_depends TABLE_NAME, I get a single result – a view. When I run either of the queries above, I get 13 results. 11 of those 13 are not what I am looking for (in addition to the table TABLE_NAME, there is a table called EX_TABLE_NAME), but there are 2 valid results – a view and a stored procedure.
sp_help returns 0 results.
Tried the first SQL Script given. Not sure how much it makes sence. Using ‘LIKE’ means you’ll get way lot more results than you need. And, removing ‘LIKE’ and using ‘=’ apparently doesn’t give any o/p.
sp_depends on the other hand gives just the results that you need.
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
The code avaialabe at the following link will search for a specific string and return table and column names in the current database
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 ?
Use this
EXEC sp_help ‘your table name’
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.
Create a procedure with input parameters. Execute it by supplying the values from the java application
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
It is not advisable to rename a table which is refered in other places. Why do you want to do this?
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.
Thanks….this is really useful
really works great! thanks!
A note of caution:
This WON’T find occurrances in encrypted objects. Nearly got myself into troubles there…
Here is how you check if you have any encrypted objects in your db:
SELECT DISTINCT(c.id), name, type
FROM syscomments c
INNER JOIN sysobjects o ON o.id=c.id
WHERE c.encrypted = 1
In addition to my first post:
It is also nice to be able to search occurance in SQL Agent Jobs
USE msdb
SELECT command, subsystem, database_name, last_run_date, last_run_time, step_name, step_id, job_id, step_uid
FROM sysjobsteps WITH (NOLOCK)
WHERE command like @search_string
This is better
use msdb
DECLARE @search_string VARCHAR(255)
SET @search_string = ‘%belagg%’
SELECT sj.enabled AS jobEnabled,
command, step_id AS jobStep, step_name,
sj.name AS jobName, sj.description AS jobDescr,
subsystem, database_name,
last_run_date, last_run_time,
sjs.job_id, step_uid
FROM sysjobsteps sjs WITH (NOLOCK)
INNER JOIN sysjobs sj ON sj.job_id = sjs.job_id
WHERE command like @search_string
i have 10 insert comand in stored procedure and i want to get in which table name and colunm name the error is coming
The query by Jens Johanneson was really helpful to find the references to functions.
The second query by Madhavan also retrives the same
Hi.
I need a code for searching inside a .doc file which is stored in sql server database using c#.net
Use GETChunck method (or its equivalent to C#)
nice code. thanks.
Hi!! its a very nice steps.
thanks,
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.