Labels

Saturday, December 31, 2011

Find all stored procedures where a column is being used/referenced

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