USE AdventureWorks2008;
DECLARE @SchemaName sysname = N'Production';
DECLARE @TableName sysname = N'Product';
DECLARE @ColumnName sysname = N'ProductID';
SELECT QUOTENAME(refing.referencing_schema_name) +
N'.' + QUOTENAME(refing.referencing_entity_name) As SprocName
FROM sys.dm_sql_referencing_entities(QUOTENAME(ISNULL(@SchemaName,N'dbo')) +
N'.' + QUOTENAME(@TableName),'object') refing
CROSS APPLY sys.dm_sql_referenced_entities(QUOTENAME(refing.referencing_schema_name) +
N'.' + QUOTENAME(refing.referencing_entity_name), 'object') refed
WHERE EXISTS(SELECT * FROM sys.objects
WHERE refing.referencing_id = object_id and type ='P')
AND refed.referenced_schema_name = @SchemaName
AND refed.referenced_entity_name = @TableName
AND refed.referenced_minor_name = @ColumnName
ORDER BY SprocName;
No comments:
Post a Comment