Monday, May 9, 2011

Microsoft SQL Server T-SQL: Find Database Objects Dependent on a Particular Object

The following SQL query example finds the database objects dependent on a database object. Use the query on database where the object belongs.

 [T-SQL Code Snippet]

SELECT  DISTINCT SysObjects.[name] [Procedure Name] FROM SysObjects JOIN
            SysObjects [SysObj]
            JOIN SysDepends
            ON  [SysObj].id = SysDepends.depid 
            --wod_blogs can be the name of table, stored procedure, views or other database objects
            AND [SysObj].[Name] = 'wod_blogs'
ON = AND SysObjects.xtype = 'P'

Particualr conditions can be added to find particual dependent objects type, for example:-

 To find dependent Views use  AND SysObjects.xtype = 'V'

 To find dependent Constaints use AND SysObjects.xtype = 'C'

No comments:

Post a Comment