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 SysDepends.id = SysObjects.id 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