how to find sp dependency in sql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

how to find sp dependency in sql

Author

Topic

jimmy_a
Starting Member

38 Posts

Posted - 2006-02-10 : 03:47:58

V can use SP_DEPENDS to find the dependency sps..Is there any other way to find dependency sps using queries..

Can any one help me in this ...

Thnks in advance

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-02-10 : 04:38:54

deja vu

i think i responded to this awhile ago...oh well

check the tsql for that sp and modify as you wish

HTH

--------------------
keeping it simple...

jimmy_a
Starting Member

38 Posts

Posted - 2006-02-10 : 04:50:34

THSI IS THE QUERY ...

SELECT DISTINCT O.NAME
FROM MASTER.DBO.SYSOBJECTS O,MASTER.DBO.SYSDEPENDS D,MASTER.DBO.SYSUSERS S
WHERE O.ID = D.ID
AND O.XTYPE = 'P'
AND D.DEPID = (SELECT OBJECT_ID( 'SPC_AEPDtl_Sp_VwDtPSPrSeMl' ))
AND O.UID = S.UID
AND DEPTYPE < 2

If i use that query i can only able to access in master DB.. But i couldnt access from diff DB

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-10 : 05:01:07

Please don't cross post. Stick to one thread please.

----------------------------------
'KH'
everything that has a beginning has an end

jimmy_a
Starting Member

38 Posts

Posted - 2006-02-10 : 05:28:14

Sorry...Im in a urgent need for this solution..Thats y posted in both...Sorry for the Inconvenience

Kristen
Test

22859 Posts

Posted - 2006-02-10 : 06:07:32

Beware that sysdepends table is not reliable. If you drop an recreate a "child" object all "parents" that used to refer to it [in sysdepends] will no longer do so until they too are dropped and recreated


PRINT 'Create usp_ProcA'
GO
CREATE PROCEDURE dbo.usp_ProcA
AS
SELECT TOP 10 * FROM dbo.sysobjects
GO

PRINT 'Create usp_ProcB'
GO
CREATE PROCEDURE dbo.usp_ProcB
AS
EXEC dbo.usp_ProcA
GO

SELECT DISTINCT [sysdepends(1)] = O.name + '->' + C.name	-- , D.*
FROM	dbo.sysobjects AS O
	JOIN dbo.sysdepends AS D
		ON D.id = O.id
	JOIN dbo.sysobjects AS C
		ON C.id = D.depid
WHERE	O.name LIKE 'usp_Proc[AB]'
GO

PRINT 'RE-Create usp_ProcA'
GO
DROP PROCEDURE dbo.usp_ProcA
GO

CREATE PROCEDURE dbo.usp_ProcA
AS
SELECT TOP 10 * FROM dbo.sysobjects
GO

SELECT DISTINCT [sysdepends(2)]=O.name + '->' + C.name	-- , D.*
FROM	dbo.sysobjects AS O
	JOIN dbo.sysdepends AS D
		ON D.id = O.id
	JOIN dbo.sysobjects AS C
		ON C.id = D.depid
WHERE	O.name LIKE 'usp_Proc[AB]'
GO

PRINT 'Tidy up'
GO
DROP PROCEDURE dbo.usp_ProcA
GO
DROP PROCEDURE dbo.usp_ProcB
GO

Kristen

jimmy_a
Starting Member

38 Posts

Posted - 2006-02-10 : 07:54:53

Thnks for ur timely info... It works for me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources