How can I get the definition (body) of a trigger in SQL Server?

Question

Unable to find a SQL diff tool that meets my needs, I am writing my own. Between the INFORMATION_SCHEMA and sys tables, I have a mostly-complete working version. But one thing I can't find in the metadata is the definition of a trigger, you know, the actual SQL code. Am I overlooking something?

Thanks.

Thanks, Pete, I didn't know about that!

Scott, I'm working with very basic hosting packages that don't allow remote connections to the DB. I don't know from the specs on RedGate (which I can't afford anyway) whether they provide a workaround for that, and although there are also API's out there (such as the one from Apex), I didn't see the point in investing in a solution that was still going to require more programming on my part. :)

My solution is to drop an ASPX page on the site that acts as a kind of "schema service", returning the collected metadata as XML. I set up a little AJAX app that compares any number of catalog instances to a master and shows the diffs. It's not perfect, but a major step forward for me.

Thanks again!

To expand on what Pete wrote, `sp_helptext` is very useful for revealing the SQL behind any trigger, view, stored procedure, function, etc. — matt b, Sep 04 '08 at 16:04
**See Also**: [How to find a text inside SQL Server procedures / triggers?](https://stackoverflow.com/questions/674623/how-to-find-a-text-inside-sql-server-procedures-triggers) — KyleMit, May 20 '20 at 14:51

score 27 · Accepted Answer · answered Sep 04 '08 at 15:50

27

sp_helptext works to get the sql that makes up a trigger.

The text column in the syscomments view also contains the sql used for object creation.

answered Sep 04 '08 at 15:50

Pete

3,991
3
31
30

score 21 · Answer 2 · answered Feb 03 '15 at 17:45

21

SELECT     
    DB_NAME() AS DataBaseName,                  
    dbo.SysObjects.Name AS TriggerName,
    dbo.sysComments.Text AS SqlContent
FROM 
    dbo.SysObjects INNER JOIN 
        dbo.sysComments ON 
        dbo.SysObjects.ID = dbo.sysComments.ID
WHERE   
    (dbo.SysObjects.xType = 'TR') 
    AND 
    dbo.SysObjects.Name = '<YourTriggerName>'

answered Feb 03 '15 at 17:45

Sathish

1,936
4
28
38

1

**Note** - the `text` field in [`syscomments`](https://learn.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-syscomments-transact-sql) is of type `nvarchar(4000)`, so this will trim any triggers greater than 4k characters. If that's a possibility, might be worth using `OBJECT_DEFINITION(id)` – KyleMit May 20 '20 at 14:48
1

@KyleMit - Yes, but when the trigger is longer, there will be a next record with continuation. Not terribly convenient, but good enough for manual inspection. – Tomasz Gandor Jul 15 '21 at 21:03

score 18 · Answer 3 · answered Sep 04 '08 at 15:53

18

For 2005 and 2008 you can use the OBJECT_DEFINITION() function

answered Sep 04 '08 at 15:53

SQLMenace

132,095
25
206
225

2

..work for 2005 - 2012 - https://msdn.microsoft.com/en-us/library/ms176090%28v=sql.110%29.aspx – Milan Jun 18 '15 at 23:41
This makes it so easy to search for objects that reference other objects, or even more complex queries like "which SPs execute an INSERT into Table X" – Michael Bray Jan 04 '19 at 21:09

score 7 · Answer 4 · answered Mar 24 '17 at 11:52

To expand on SQLMenace's answer, here's a simple query to return all triggers and their definitions from a database:

SELECT 
    sysobjects.name AS trigger_name, 
    OBJECT_NAME(parent_obj) AS table_name,
    OBJECT_DEFINITION(id) AS trigger_definition
FROM sysobjects 
WHERE sysobjects.type = 'TR'

score 3 · Answer 5 · answered Dec 10 '19 at 11:15

you have various ways to view SQL Server trigger definition.

querying from a system view:

SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('trigger_name');

Or

SELECT OBJECT_NAME(parent_obj) [table name], 
   NAME [triger name], 
   OBJECT_DEFINITION(id) body
FROM sysobjects
WHERE xtype = 'TR'
  AND name = 'trigger_name';

definition using OBJECT_DEFINITION function:

SELECT OBJECT_DEFINITION(OBJECT_ID('trigger_name')) AS trigger_definition;

definition using sp_helptext stored procedure:

EXEC sp_helptext 
 'trigger_name';

score 0 · Answer 6 · answered Jan 30 '18 at 06:27

this query return trigger with its name and body.

Select 
    [tgr].[name] as [trigger name], 
    [tbl].[name] as [table name] , 
    OBJECT_DEFINITION(tgr.id) body

    from sysobjects tgr 

    join sysobjects tbl
    on tgr.parent_obj = tbl.id

WHERE tgr.xtype = 'TR'

How can I get the definition (body) of a trigger in SQL Server?

6 Answers6