SQL Server 2005 - Find Which Stored Procs Run To A Particular Table

Question

• Is there a quick way that I can find which stored procedures run to a particular table in my database?
• The database is very large with lots of tables and SPROCS....

Answer 1

If you want to restrict the search to stored procedures then you can do this:

SELECT name
FROM sys.objects
WHERE type = 'P'
    AND OBJECT_DEFINITION(object_id) LIKE '%name_of_your_table%'
ORDER BY name

If you wanted to include other SQL modules -- for examples, functions, triggers, views etc -- then you could alter the query to do WHERE type IN ('P', 'FN', 'IF', 'TF', 'V') etc, or use the alternative given in Martin's answer.

Answer 2

A Combination of looking at dependencies and looking at the text of your objects should do it.

select * from sys.sql_modules
where 
definition like '%tableName%'
/*AND objectproperty(object_id,'isprocedure')=1 to just look at procedures*/

exec sp_depends 'tableName'