Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Finding Real Dependencies

By Stefan Popovski,

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.

Total article views: 9099 | Views in the last 30 days: 8
 
Related Articles
FORUM

Parameterised Input stored procedure

Parameterised Input stored procedure

FORUM

xml input for stored procedure

xml input for stored procedure

FORUM

create Procedure within Procedure

create Procedure within Procedure

FORUM

How to make input parameters mandatory in Stored Procedure ?

How to make input parameters mandatory in Stored Procedure ?

FORUM

Execute Stored Procedure with Select Statement as Input Parameters

Execute Stored Procedure with Select Statement as Input Parameters

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones