SP and function dependencies


Finding dependencies in SPs is always pain. This SP scans the SP for calls to SPs, functions and other stuff (you can see it in the where clause "type in ('p','fn','if','x','ft','fs','pc')". It ignores commented out pieces of text. It uses a function fn_SplitBy included bellow, which splits the text (from syscomments) in char(10) delimited rows. The results are stored in a table "tree", which is created if it doesn't exist. To "pretty print" the dependency tree I'm using my previous published DBA_PrintTree stored proc. Actually, the published one is a bit different, so I'm including the modified one here.

The usage looks like:

exec dba_GetDependencies 'sp_name'



--- sql ----

create procedure [dbo].[DBA_PrintTree] (@root int)
set nocount on;

with btree (treeid,parenttreeid,nodename,level)
select treeid,parenttreeid,nodename,0 as level from tree
where treeid=@root
union all
select b1.treeid,b1.parenttreeid,b1.nodename,level+1 from tree b1 join btree b2 on b2.treeid=b1.parenttreeid

select treeid,parenttreeid,nodename,level into #t from btree order by level, parenttreeid


declare @stack table(d int identity(1,1) primary key,node int)
declare @t table(treeid int,parenttreeid int,nodename varchar(64),level int)
declare @parent int,@nodename varchar(64),@level int,
@bid int,@bparent int
set @parent=@root
insert @t select * from #t where treeid =@root
select @nodename=nodename from @t
print @nodename
delete #t where treeid =@root
insert @stack values(@root)


while exists(select top 1 node from @stack)
while exists (select treeid from #t where parenttreeid=@parent)
select top 1 @bid=treeid,@bparent=parenttreeid,@nodename=nodename,@level=level from #t
where parenttreeid=@parent order by nodename
insert @t values(@bid,@bparent,@nodename,@level)
print space(@level*5)+@nodename
delete #t where treeid=@bid
insert @stack values(@bid)
set @parent=@bid
delete @stack where d in (select max(d) from @stack)
select top 1 @parent=node from @stack order by d desc

select * from @t
drop table #t



create function [dbo].[fn_splitby](
@in nvarchar(max),
@by varchar(10)
returns @t table (id int identity(1,1),split nvarchar(max))
as begin
declare @i int, @bylen int, @insz int
set @bylen = len(@by)
set @insz = len(@in)
set @i = charindex(@by, @in)
while (@i > 0)
insert into @t values (left(@in, @i - @bylen))
set @in = substring(@in, @i + @bylen, @insz)
set @i = charindex(@by, @in)
insert into @t values (@in)


create procedure dba_GetDependencies @sp sysname,@id int=null
declare @x varchar(max),@cstart int,@cend int,@maxparentid int
set nocount on

if object_id('tree') is null
create table tree (treeid int identity(1,1),parenttreeid int,[nodename] sysname,rootid int,processed bit)

-- get the source SP & merge records into a string ---
select text into #sp from syscomments  where  id=object_id(@sp)
set @x=''
while exists(select text from #sp)
select top 1 @x=@x+text from #sp
delete top(1) #sp
drop table #sp

create table #clines (cstart int,cend int)

--- split the string into lines
select * into #t from dbo.fn_splitby(@x,char(10))

--- get the commented out line ranges
while exists(select split from #t where charindex('/*',split)>0)
select top 1 @cstart=id from #t where charindex('/*',split)>0
select top 1 @cend=id from #t where charindex('*/',split)>0
insert #clines values(@cstart,@cend)
delete #t where id in(@cstart,@cend)

drop table #t

-- get all objects of given type referenced in the @sp which are not commented out by '--'
select id,o.name,split into #tt from dbo.fn_splitby(@x,char(10)) s 
join sys.objects o on charindex(o.name,split)>0 
where o.type in ('p','fn','if','x','ft','fs','pc')
and substring(split,charindex(o.name,split)+len(o.name),1) not like '[a-z]'
and substring(split,charindex(o.name,split)+len(o.name),1) not like '[0-9]'
and substring(split,charindex(o.name,split)+len(o.name),1) not like '[@,#]'
and  o.name<>@sp and (charindex(o.name,split) < charindex('--',split) or charindex('--',split)=0)

-- delete all objects between /*.. */  
delete #tt from #clines c join #tt t on id=cstart where charindex('/*',split)<charindex([name],split)
and charindex('*/',split)>charindex([name],split)
delete #tt from #clines c join #tt t on id=cend where charindex('*/',split)>charindex([name],split)
and charindex('/*',split)<charindex([name],split)

delete #tt from #clines c join #tt t on id between cstart+1 and cend-1

select distinct name into #ttt from #tt
drop table #tt

select @maxparentid=max(parenttreeid) from tree
if @maxparentid is null
insert tree values (null,@sp,1,1)
insert tree  select distinct tr.treeid ,tt.name,1,0 from #ttt tt,tree tr where tr.nodename=@sp and treeid=isnull(@id,1)

-- if we got anything from the extraction above, start processing
if @@rowcount >0
while exists(select * from tree where isnull(processed,0)=0) and @@nestlevel<31 
select top 1 @sp=nodename,@id=treeid from tree where isnull(processed,0)=0
update tree set processed=1 where nodename =@sp and treeid=@id

exec dba_GetDependencies @sp,@id
if  @@nestlevel<31 
update tree set processed=1 where nodename =@sp and treeid=@id

drop table #clines
drop table #ttt



2.33 (3)




2.33 (3)