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)
as
set nocount on;

with btree (treeid,parenttreeid,nodename,level)
as
(
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)
begin
while exists (select treeid from #t where parenttreeid=@parent)
begin
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
end
delete @stack where d in (select max(d) from @stack)
select top 1 @parent=node from @stack order by d desc
end

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)
begin
insert into @t values (left(@in, @i - @bylen))
set @in = substring(@in, @i + @bylen, @insz)
set @i = charindex(@by, @in)
end
insert into @t values (@in)
return
end
-------------

 

create procedure dba_GetDependencies @sp sysname,@id int=null
as
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)
begin
select top 1 @x=@x+text from #sp
delete top(1) #sp
end
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)
begin
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)
end

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
begin 
while exists(select * from tree where isnull(processed,0)=0) and @@nestlevel<31 
begin
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
end
end
if  @@nestlevel<31 
update tree set processed=1 where nodename =@sp and treeid=@id


drop table #clines
drop table #ttt

return

Rate

2.33 (3)

Share

Share

Rate

2.33 (3)