identify stored procedures and triggers that carries a particular string in sql server – Education Career Blog

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

,

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

,

Download SQL Digger – works a treat

http://www.sqldigger.com/

,

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

Leave a Comment