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')
hi Pandian Sathappan
ReplyDeleteI am always using sp_depends system procedure, But this script will help me more... :)
Thank you
sudha