Wednesday, July 31, 2013

Script to find dependent objects

I found that sp_depends does not guarantee correct results, Since Sql 2008 have provided useful way to get correct results. Here is sample script.

For finding Referencing Entities. Typically usage is to find out where does particular table is used in entire database.
SELECT  distinct referencing_schema_name, referencing_entity_name
FROM sys.dm_sql_referencing_entities ('Object_Name', 'object');

For finding Referenced Entities.Typicall usage is to find out tables which are used in stored procedure.
SELECT  distinct referenced_schema_name, referenced_entity_name
FROM sys.dm_sql_referenced_entities ('Object_Name', 'object');

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.