If you want to search triggers for text in SQL Server 2005, here is how you can do it.  You would execute this in the case that you are looking for a trigger that updates a certain table, but you cannot find it, and you do not want to go through all the tables one by one, here is what you can execute:

SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%your_search_here%' AND OBJECTPROPERTY(id, 'IsTrigger') = 1
GROUP BY OBJECT_NAME(id)

And here is how you can search SQL server stored procedures (external link).

Other Interesting Posts

 

3 Responses to Search Trigger Text SQL Server 2005

  1. 2qvbk217zqc1val@mail.misterpinba says:

    personlaly I prefer:

    SELECT text
    FROM syscomments
    WHERE [text] LIKE ‘%trigger%’
    – if you want a trg attached to specific table
    AND [text] LIKE ‘%On Tablename%’

  2. Nick says:

    or use following statemant. it will produce same results, just fewer lines in T-SQL
    SELECT * FROM sys.triggers WHERE name LIKE ‘%your_search_here%’

  3. STU says:

    Would be more helpful if it list the tables too. Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

*


+ 6 = 11

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>