SQL Server 2005 - Find Which Stored Procedures Run in a Specific Table

Question

• Is there a quick way that I can find which stored procedures are executed before a specific table in my database?
• The database is very large with many tables and SPROCS ....

Answer 1

If you want to limit the search to stored procedures, 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 want to include other SQL modules - for example, functions, triggers, views, etc. - then you can change the request to make WHERE type IN ('P', 'FN', 'IF', 'TF', 'V') , etc., or use the alternative specified in Martin's Answer .

Answer 2

The combination of finding dependencies and viewing the text of your objects should do this.

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