SP_DEPENDS,SYS.SQL_DEPENDENCIES,SYS.SYSDEPENDS

We can find Object Dependencies on three ways. Means Which Procedure is Dependent on Which Table, Procedure,
Which trigger is dependent on which Table. These things we can findout in three Ways.

1) SP_DEPENDS
2) SYS.SQL_DEPENDENCIES
3) SYS.SYSDEPENDS
Go through the below example:
CREATE PROCEDURE IndependentProc
AS
SELECT * FROM SYS.OBJECTS—You can create any table here and give the name.
GO
CREATE PROC DepedentProc
AS
EXEC IndependentProc

Check the results by using above system catalogue views and procedures
SP_DEPENDS
1) SP_DEPENDS IndependentProc
2) SP_DEPENDS DepedentProc

SYS.SQL_DEPENDENCIES:
SELECT * FROM SYS.SQL_DEPENDENCIES
WHERE OBJECT_ID=210099789 –Give Here DepedentProc ID
Note:If you have Drop dependent stored procedure.Then the above query does not give you any result.
SYS.SYSDEPENDS:
SELECT OBJECT_NAME(ID) as DependentObject,OBJECT_NAME(DEPID) as IndependentObject FROM SYS.SYSDEPENDS
WHERE ID=210099789
Note:There maybe so many dependent objects.So we want know about our example Only.So we have given ID.

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server