Wednesday, January 20, 2016

To know table name where used in database

DECLARE @TableName varchar(100)
SET @TableName = 'Users'
SELECT
SourceObject = OBJECT_NAME(sed.referencing_id),
ReferencedDB = ISNULL(sre.referenced_database_name, DB_NAME()),
ReferencedObject = sre.referenced_entity_name,
ReferencedColumnID = sre.referenced_minor_id,
ReferencedColumn = sre.referenced_minor_name
FROM sys.sql_expression_dependencies sed
CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id)
+ '.' + OBJECT_NAME(sed.referencing_id), 'OBJECT') sre
WHERE sed.referenced_entity_name = @TableName
AND sre.referenced_entity_name = @TableName And sre.referenced_minor_name is not null

No comments: