Object Dependencies - various level

How to identify, What are all the objects being referred/referencing from/to various sources in current database.

1. Schema Bound
2. Non-Schema Bound
3. Cross-Database
4. Cross-Server

SELECT referenced_entity_name [Referenced Objects],
CASE referencing_class WHEN 1 THEN 'Object or Column'
WHEN 12 THEN 'Database DDL Trigger'
WHEN 13 THEN 'Server DDL Trigger'
END [Referencing Type],
CASE is_schema_bound_reference WHEN 1 THEN 'Referenced entity is schema-bound'
ELSE 'Referenced entity is non-schema-bound' END [Schema Bound],
CASE referenced_class WHEN 1 THEN 'Object or Column'
WHEN 12 THEN 'Database DDL Trigger'
WHEN 13 THEN 'Server DDL Trigger'
END [Referenced Type],
referenced_server_name [Object Referred From - Server],
referenced_database_name [Object Referred From - Database],
referenced_schema_name [Object Referred From - Schema]
FROM sys.sql_expression_dependencies
GO


(OR)

What are all the Object(s) being referred by "TEST1" object ?
SELECT referenced_server_name [Object From - Server],
referenced_database_name [Object From - Database],
referenced_schema_name [Object From - Schema],
referenced_entity_name [Object From - Table]
FROM sys.dm_sql_referenced_entities('dbo.TEST1','Object')


What are all the Object(s) referencing the "TEST1" object ?
SELECT * FROM sys.dm_sql_referencing_entities('dbo.Test1','Object')

1 comment:

  1. hi Pandian Sathappan

    I am always using sp_depends system procedure, But this script will help me more... :)

    Thank you
    sudha

    ReplyDelete