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 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?
What do you mean by it?
Give an example
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
Did you mean printing the content of the procedure?
EXEC sp_helptext ‘procedure_name’
–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
Thanks for the query!
This was very useful. Thanks…Keep up the good work
Very useful for me, thanx!
Query from Jens Johanneson has saved me lot of hours,
Great Work Jens
This is actually wrong, this query returns all database objects (not just stored procedures) and will return any database object that contains the string. The string could be in a comment or it could be a substring in another table name. The potential returning incorrect results is quite high.
can i generate table script using query analyzer or stored procedure,we right click and select generate script at table object can we do the same using any sp
Hi All ,
I am new to the SP.
My question is how to debug the SP,if it is a 1000 of query inside?
and one more question- if the SP contains any DML statements, how to debug?
Request you to reply me ASAP?
Thanks
Exec SPName
Ex:
Exec SpEmployee
Thanks @Venkat.
Hi All,
I need to change a table column data type. Prior to that, I would like to get all the dependencies on this column like references,key,indexes,constraints etc.Also, column references in stored procedures,views etc.
Is there a easy way to do it in SQL server 2008?
Thanks
Hi Pinal,
I read all the comments and reply
I have a question.
I successfully executed the Stored Procedure using .net
We have a website and 80 unique customer ID
Can you please tell me how can I call stored procedure to these 80 unique ID and how to recognize parameters
I appreciate your help and support
Thanks
Ankit
Thanks – very useful
Hi Pinal,
How to find which column refer most in system procedure in sql server…
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE ‘%tablename%’ –-it will not return correct data if table name is from key word
EXEC sp_depends tablename –This will return the correct data
Hi, I would like to find all the tables a particular stored procedure is using. Those tables may be from different databases in server.
How can i check if a row exists in a table using stored procedure. If a row does exist then how will i update the table
thanks yar. thanks a lot.