Report which SP calls another SP

[ignoreme]

I am looking for a way to evaluate which stored procedures call other stored procedures.

I inheirited another project, and it has 100 or more stored procedures. I want to find out which stored procedures call which stored procedures(if any).

Any suggestions?

 

[nigelrivett]

Have a look in sysdepends - not very reliable but might help.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[philhege]

Here's a sample SP that spins through the first 15 SPs it encounters in the schema and saves every line with the EXEC command on it:

declare @l_croutine_name sysname
select top 15 routine_name, 0 as processed into #spinnit
from information_schema.routines
where routine_type = 'PROCEDURE'
create table #mysptext (spname sysname NULL, sptext varchar(2000))
while (select count(*) from #spinnit where processed = 0) > 0
begin
select top 1 @l_croutine_name = routine_name from #spinnit where processed = 0
insert #mysptext (sptext) exec sp_helptext @l_croutine_name
update #mysptext set spname = @l_croutine_name where spname is NULL
update #spinnit set processed = 1 where routine_name = @l_croutine_name
end
select * from #mysptext
where charindex('exec',sptext) > 0
drop table #spinnit
drop table #mysptext

Enjoy.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.

 

[nigelrivett]

You could do something similar by using syscomments
But you will also get xp_sendmail, dynamic sql, ....
It will also miss anything that is on multiple line.

maybe something like

select [sp] = object_name(c.id), [calls] = o.name
from syscomments c
join sysobjects o2
on c.id = o2.id
and o2.xtype = 'P'
join sysobjects o
on o.xtype = 'P'
and c.text like '%' + o.name + '%'

This will get false positives
e.g. if you have an sp called ect it will pick up every select statement.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[NathanGriffiths]

another variation on this theme, i used this a while ago and it was slow but then again I only needed to run it once to build up a table of sp's and references;

declare csSPName cursor local fast_forward for 
	select Routine_Name 
        from Information_Schema.Routines 
        where Routine_Type = 'procedure' 
        and Routine_Name not like 'dt_%' 
        order by Routine_Name

if object_id('spref') is null 
	create table spref (
           [Procedure name] varchar(200), 
           [Referenced in procedure] varchar(200), 
           [Sample code from Referencing Procedure] varchar(200))
else 
	truncate table spref

open csSPName

declare @spname varchar(200)

fetch next from csSPName into @spname

while @@fetch_status = 0

begin
	
	insert into 
		spref
	select 
		@spname, 
		Routine_Name,
		'..' + Substring(Routine_Definition, PatIndex('%' + LTrim(RTrim(@spname)) + '%', Routine_Definition) - 30, 100)
	from 
		Information_Schema.Routines 
	where 
		Routine_Definition like '%' + LTrim(RTrim(@spname)) + '["), ]%'
	and
		Routine_Name <> @spname

	fetch next from csSPName into @spname

end


-------------------
select * from spref

Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------

 

[nigelrivett]

Odd - why use a cursor. It's in effect the same as the query I gave but using the schema views and could be written in the same way.

Suspect it would have problems if the sp call was followed by crlf or tab or had [] round the identifier and would have the same problem with a sp named ect.

something like

select
spname,
Routine_Name,
'..' + Substring(Routine_Definition, PatIndex('%' + LTrim(RTrim(spname)) + '%', Routine_Definition) - 30, 100)
from
Information_Schema.Routines ,
(select spname = Routine_Name
from Information_Schema.Routines
where Routine_Type = 'procedure'
and Routine_Name not like 'dt_%'
) a
where
Routine_Definition like '%' + LTrim(RTrim(spname)) + '["), ]%'
and
Routine_Name <> spname


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[NathanGriffiths]

Yup, as I say, its a variation on the theme, i.e. similar to yours. As for the cursor, well it was a quite a while ago when I wrote this.. ;-) I found the sample code column useful though.
Is there some kind of tool on the market for modelling things like this in SQL Server?

Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------