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.
Is there any system sp in sql server to get the create, insert or select script of a table instead of right clicking the table anme and selecting the particular query type(Create to, insert to or select to) as selecting from the menu takes long tme to fetch the query?
Would you please help me how i can find out Dependencies of views on stored procedure?
following query returns the dependencies of table but does not return the dependencies of views?
select distinct referenced_major_id from sys.sql_dependencies where
object_id = {0} //table object id
Hi Friends,
I need your help, to get the dependency table list of the stored procedure.
USE dbA
Create Procedure usp_Test
As
BEGIN
If exists( select * from tblA)
SELECT * FROM tblB
END
When I execute, sp_depends usp_Test
It displays the tblB and tblA table lists
But when I call other database table names inside my procedure, it’s not showing the results, instead of it throws the error
‘Object does not reference any object, and no objects reference it.’
USE dbA
Create Procedure usp_Test
As
BEGIN
If exists( select * from dbB..tblC)
SELECT * FROM dbB..tblD
END
Could you please help me to get these tables (tblC and tblD)
I am using SQL Server 2005 not 2008 :-(
Manesh P
Hi, We will have upgradation from SQL 2000 to SQL 2005. During running of upgrade advisior I got this messgae :: “Outer join operators *= and =* are not supported in 90 compatibility mode”. I was wonder will it be possible for us to find this kind of query. Pinal your query really works but I can not search for views. So will you help me out?
Thanks
Sneha
[...] SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored procedure [...]
This query was very useful to me..
thank u very much..
Nice query. Thanks a lot
This query is used by me today and it served my purpose. Thank you :)
Hi Pinal,
I am from India and love to work in desired way. Its nice one to find out the table name from all sp into the system.
Cheers,
Niyaz Ahmad
To get it to work, I had to change sc.TEXT to sc.text
is it possible to get store procedure parameter(s) dynamically in sql server 2005?
Hello Pinal,
Great Piece of work.
How can get the printed version of the query if we are using “sp_helptext” ?
Looking forward to your sincere reply for the same.
With
Best Regards
Pallav Vidwans
–usp_dts_find_object_in_routine
–object contains table, view,stored procedure and user defined function)
create procedure usp_dts_find_object_in_routine
(
@routine_name nvarchar(200)=null
)
as
–temp variables for retrieving data from cursor
declare @temp_routine_name nvarchar(200)
declare @temp_routine_type nvarchar(200)
declare @temp_routine_definition nvarchar(4000)
declare @temp_created datetime
declare @temp_last_altered datetime
declare @temp_referred_routine_name nvarchar(200)
declare @temp_table_name nvarchar(200)
–declare @temp_table_type nvarchar(20)
–create routine container
if(object_id(‘temp..#temp_routines’) is null)
begin
create table #temp_routines
(
id int identity(1,1) primary key,
routine_name nvarchar(200),
referred_table nvarchar(4000),
referred_routine nvarchar(4000),
–table_type nvaarchar(20),
routine_type nvarchar(20) null,
routine_definition nvarchar(4000) null,
created datetime null,
last_altered datetime null
)
end
else
begin
truncate table #temp_routines
end
–declare cursor for routines
declare cur_routine_list cursor for
select routine_name,routine_type,routine_definition,created,last_altered
from information_schema.routines
where routine_name=case when @routine_name is null or ltrim(rtrim(@routine_name))=” then routine_name else @routine_name end
order by routine_type
open cur_routine_list
–find tables which is referred in routines
fetch next from cur_routine_list into @temp_routine_name,@temp_routine_type,@temp_routine_definition,@temp_created,@temp_last_altered
while(@@fetch_status=0)
begin
if (not exists(select * from #temp_routines where routine_name=@temp_routine_name))
begin
insert into #temp_routines(routine_name,routine_type,routine_definition,created,last_altered)
values(@temp_routine_name,@temp_routine_type,@temp_routine_definition,@temp_created,@temp_last_altered)
end
else
begin
update #temp_routines
set routine_name=@temp_routine_name,
routine_type=@temp_routine_type,
routine_definition=@temp_routine_definition,
created=@temp_created,
last_altered=@temp_last_altered
where routine_name=@temp_routine_name
end
–loop table list
declare cur_table_list cursor for
select table_name from information_schema.tables
open cur_table_list
fetch next from cur_table_list into @temp_table_name
while(@@fetch_status=0)
begin
if(charindex(@temp_table_name,@temp_routine_definition)>0)
begin
update #temp_routines
set referred_table=isnull(referred_table+’,',”)+@temp_table_name
where routine_name=@temp_routine_name
end
fetch next from cur_table_list into @temp_table_name
end
close cur_table_list
deallocate cur_table_list
–loop referred routine list
declare cur_referred_routine_list cursor for
select routine_name
from information_schema.routines
order by routine_type
open cur_referred_routine_list
fetch next from cur_referred_routine_list into @temp_referred_routine_name
while(@@fetch_status=0)
begin
if(charindex(@temp_referred_routine_name,@temp_routine_definition)>0 and @temp_referred_routine_name@temp_routine_name)
begin
update #temp_routines
set referred_routine=isnull(referred_routine+’,',”)+@temp_referred_routine_name
where routine_name=@temp_routine_name
end
fetch next from cur_referred_routine_list into @temp_referred_routine_name
end
close cur_referred_routine_list
deallocate cur_referred_routine_list
fetch next from cur_routine_list into @temp_routine_name,@temp_routine_type,@temp_routine_definition,@temp_created,@temp_last_altered
end
close cur_routine_list
deallocate cur_routine_list
select *
from #temp_routines
order by last_altered,routine_name