Do you remember the differences between SQL 6.5 and SQL 2000 about creating a procedure that calls another

procedure that doesn't exist?

Server 6.5 would not allow the procedure to be created when it depends upon a

non-existing procedure.

On the other hand, SQL Server 7.0 and 2000 will allow the procedure to be

created, and the SP_DEPENDS system procedure will not report correct results.

If we run following script:

USE Northwind
go
CREATE PROCEDURE proc1 
AS exec proc2
GO
CREATE PROCEDURE proc2 
AS exec proc3
GO
CREATE PROCEDURE proc3
AS exec proc4
GO
CREATE PROCEDURE proc4 
AS exec proc5
GO
CREATE PROCEDURE proc5
AS exec proc6
GO 

We receive sql messages:

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'proc2'. 
The stored procedure will still be created.
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'proc3'. 
The stored procedure will still be created.
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'proc4'. 
The stored procedure will still be created.
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'proc5'. 
The stored procedure will still be created.
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'proc6'. 
The stored procedure will still be created. 

In sysdepends table will not exist dependencies for "proc(i) 

- proc(i+1)".  We can check that with this statement

 which should yield zero records.

select * from sysdepends where object_name(id) like 'proc%'

So I can't trust system table sysdepends. However, sometimes I need real information about dependencies, especially between

stored procedures, to get the real processing flow.

So I developed a sql statement to show store procedure dependencies in one

database by searching sysobjects and syscomments system tables.

At first I'm creating a recursive function which will return sp text without comments. This function erases up to 160 line comments and 160 block comments -

32 nested level of recursive function and five replacement in every function

call. We can increase this number if we need it.

CREATE Function funProcWithoutComments
(@Input VARCHAR(4000))
RETURNS VARCHAR(4000) -- tuka da se proveri
BEGIN 
DECLARE @Output VARCHAR(4000)
DECLARE @i INT
If @Input NOT LIKE '%--%' and @Input NOT LIKE '%/*%*/%'
BEGIN
SET @Output = REPLACE(@Input , CHAR(10) + CHAR(13) , '')
RETURN @Output
END
ELSE
BEGIN
SET @input = @input + char(13)
set @i = 1
while @i <= 5
begin
IF charindex('/*',@Input) > 0 and charindex('*/',@Input, charindex('/*',@Input)) - charindex('/*',@Input) + 2 > 0
BEGIN
SET @Input = REPLACE( @Input,
substring( @Input,
charindex('/*',@Input),
charindex('*/',@Input, charindex('/*',@Input)) - charindex('/*',@Input) + 2) 
, '') 
END
set @i = @i+1
end
set @i = 1
while @i <= 5
begin
IF charindex('--',@Input) > 0 and charindex(char(13),@Input,charindex('--',@Input)) - charindex('--',@Input) +2 > 0 
BEGIN
SET @Input = 
REPLACE( @Input,
substring(@Input ,
charindex('--',@Input),
charindex(char(13),@Input,charindex('--',@Input)) - charindex('--',@Input) +2 ) 
, '') 
END
set @i = @i+1
end
SET @Output = dbo.funProcWithoutComments (@Input)
END
RETURN @Output
END
Then I find all the dependencies in the database with the following statement: 

SELECT so1.id as ID, 
so1.name As ProcName,
dbo.funProcWithoutComments(sc.text) as ProcText
into #T1
FROM sysobjects so1 
inner join syscomments sc
on so1.id = sc.id 
WHERE
so1.type = 'P' and so1.name not like 'dt_%'
-------------------------------------------------------------
select left(#T1.ProcName,30), left(T2.DependOnProc,30) from
#T1
inner join
(select id, name as DependOnProc from sysobjects where type = 'P' and name not like 'dt_%') T2
on #T1.ID <> T2.ID
WHERE #T1.ProcText LIKE '%' + T2.DependOnProc + '[' + char(9)+ char(10)+ char(13)+ char(32) + ']%'
and
CHARINDEX(#T1.ProcName, #T1.ProcText) 
<>
CHARINDEX(T2.DependOnProc, #T1.ProcText,CHARINDEX(#T1.ProcName, #T1.ProcText)+1) 
order by 1,2
--------------------------------------------------------------
drop table #T1 

Running this statement in Northwind database will yield:

ProcName             DependOnProc 

---------------------------------

proc1                    

proc2

proc2                    

proc3

proc3                    

proc4

proc4                    

proc5

In other words ProcName calls DependOnProc.

The statement excludes system procedures with prefix 'dt_'.

In addition these dependencies can be used to create a hierarchical report

useful for documentation and error handling especially when we use nested

transactions.

I will explain such examples in my next article.