How to find the name of stored procedure, based on table name search, using SQL Server 2008?

Question

I want to find all of the stored procedures where a particular table is being used. There are lots of stored procedures in the database, so it's not feasible to check each procedure.

Is there any way to use a search query so that I can find the stored procedures?

I have tried this code:

SELECT distinct so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '% RejectionReason %'

Where RejectionReason is my table name, but it shows all procedures where RejectionReason is used as column name, so that doesn't work.

Answer 1

SELECT o.name, o.type_desc, p.name, p.type_desc
FROM sys.sql_dependencies d
INNER JOIN sys.objects o
    ON d.object_id = o.object_id
INNER JOIN sys.objects p
    ON d.referenced_major_id = p.object_id
    AND o.name = 'RejectionReason'

or

SELECT o.name, t.TABLE_NAME, c.text 
  FROM syscomments c 
  JOIN sysobjects o 
    ON c.id = o.id
  JOIN INFORMATION_SCHEMA.Tables t
    ON  c.text LIKE '%RejectionReason%' 

or

EXEC sp_depends @objname = N'RejectionReason';

if none of those help you check this blog: http://blog.sqlauthority.com/2010/02/04/sql-server-get-the-list-of-object-dependencies-sp_depends-and-information_schema-routines-and-sys-dm_sql_referencing_entities/