SQL Server Planet

One new feature that SQL Server 2008 offers is more reliable dependency information. SQL Server 2005 offered a DMV (dynamic management view) called sys.sql_dependencies. It is now replaced by a more reliable sys.sql_expression_dependencies.

The following script will show all the procedures that reference a given table name, along with the columns the procedure references also. Please note, this does not take into account any dynamic SQL.

USE MYDatabase
GO

DECLARE @TableName varchar(100)
SET @TableName = 'mytable'

SELECT
 SourceSchema                  = OBJECT_SCHEMA_NAME(sed.referencing_id)
 ,SourceObject                 = OBJECT_NAME(sed.referencing_id)
 ,ReferencedDB                 = ISNULL(sre.referenced_database_name, DB_NAME())
 ,ReferencedSchema             = ISNULL(sre.referenced_schema_name,
OBJECT_SCHEMA_NAME(sed.referencing_id))
 ,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