Find Dependent Objects

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