CODEWITHSUNDEEP
sql-server-2005t-sqlstored-procedures
Goober
Goober

Reputation: 13508

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

Upvotes: 4

Views: 2819

Answers (2)

LukeH
LukeH

Reputation: 269368

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.

Upvotes: 5

Martin Smith
Martin Smith

Reputation: 453287

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'

Upvotes: 3

Related Questions