I need a query which will return all the stored procedure which has a particular table called in it. For eg we have table called PRBuild_DealAll_DT and we need to know which all stored procs are calling this table.

 Also

from management studio , you can view dependency of the table

- objects that depends on <mytable>
- objects on which <mytable> depends

Thanks for responding!! But when I tried running the above query it did not return any procedure name which is not possible because i am sure that there are few procedures which are calling this table.

if the name of table within dynamic sql statement in the stored procedure , you can't  get  any dependency.

bz, when you try to create sp , while compiling ,sql server  check dependency except for dynamic sql.

Try:

SELECT DISTINCT o.name ,o.xtype
    FROM syscomments c
            INNER JOIN sysobjects o ON c.id=o.id
    WHERE c.TEXT LIKE '%tablename%'

Thanks a lot this was helpful..