identify stored procedures and triggers that carries a particular string in sql server

Question

Hi Guys,

In sql server 2005

I need to identify all the stored procedures and triggers where a particular table name is used

for ex: i want to search for "Table1"

Please advise

Answer 1

Download SQL Digger - works a treat

http://www.sqldigger.com/

Answer 2

See here : http://stackoverflow.com/questions/2218807/if-i-want-to-find-whats-referencing-an-object-in-sql-server-is-searching-syscom

Answer 3

Try one of these:

select sysobjects.name from syscolumns left join sysobjects on sysobjects.id = syscolumns.id where syscolumns.name like '%Table1%' order by 1

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Table1%'
GO

Answer 4

In SQL2005:

SELECT Name 
FROM sys.procedures 
WHERE OBJECT_DEFINITION(object_id) LIKE '%Table1%' 

Or in SQL Server Management Studio right-click on the table and choose "View dependencies", but this wont find procs that reference Table1 using dynamic SQL.

More info: http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html