<p>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 <em>definition</em> of a trigger, you know, the actual SQL code.  Am I overlooking something?</p>

<p>Thanks.</p>

<hr>

<p>Thanks, Pete, I didn't know about that!</p>

<p>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. :)</p>

<p>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.</p>

<p>Thanks again!</p>

[{'answer_id': 44045, 'author': 'Pete', 'author_id': 76, 'author_profile': 'https://Stackoverflow.com/users/76', 'pm_score': 6, 'selected': True, 'text': '<p>sp_helptext works to get the sql that makes up a trigger.</p>\n\n<p>The text column in the syscomments view also contains the sql used for object creation.</p>\n'}, {'answer_id': 44050, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 4, 'selected': False, 'text': '<p>For 2005 and 2008 you can use the <a href="http://msdn.microsoft.com/en-us/library/ms176090.aspx" rel="noreferrer">OBJECT_DEFINITION()</a> function</p>\n'}, {'answer_id': 28305390, 'author': 'Sathish', 'author_id': 3789892, 'author_profile': 'https://Stackoverflow.com/users/3789892', 'pm_score': 4, 'selected': False, 'text': "<pre><code>SELECT     \n    DB_NAME() AS DataBaseName,                  \n    dbo.SysObjects.Name AS TriggerName,\n    dbo.sysComments.Text AS SqlContent\nFROM \n    dbo.SysObjects INNER JOIN \n        dbo.sysComments ON \n        dbo.SysObjects.ID = dbo.sysComments.ID\nWHERE   \n    (dbo.SysObjects.xType = 'TR') \n    AND \n    dbo.SysObjects.Name = '&lt;YourTriggerName&gt;'\n</code></pre>\n"}, {'answer_id': 42998834, 'author': 'Loftx', 'author_id': 89941, 'author_profile': 'https://Stackoverflow.com/users/89941', 'pm_score': 3, 'selected': False, 'text': "<p>To expand on SQLMenace's answer, here's a simple query to return all triggers and their definitions from a database:</p>\n\n<pre><code>SELECT \n    sysobjects.name AS trigger_name, \n    OBJECT_NAME(parent_obj) AS table_name,\n    OBJECT_DEFINITION(id) AS trigger_definition\nFROM sysobjects \nWHERE sysobjects.type = 'TR' \n</code></pre>\n"}, {'answer_id': 48515193, 'author': 'mehdi', 'author_id': 1831567, 'author_profile': 'https://Stackoverflow.com/users/1831567', 'pm_score': 0, 'selected': False, 'text': "<p>this query return trigger with its name and body.</p>\n\n<pre><code>Select \n    [tgr].[name] as [trigger name], \n    [tbl].[name] as [table name] , \n    OBJECT_DEFINITION(tgr.id) body\n\n    from sysobjects tgr \n\n    join sysobjects tbl\n    on tgr.parent_obj = tbl.id\n\nWHERE tgr.xtype = 'TR'\n</code></pre>\n"}, {'answer_id': 59265974, 'author': 'Reza Jenabi', 'author_id': 9549856, 'author_profile': 'https://Stackoverflow.com/users/9549856', 'pm_score': 2, 'selected': False, 'text': "<p>you have various ways to view SQL Server trigger definition.</p>\n\n<p>querying from a <strong>system view:</strong></p>\n\n<pre><code>SELECT definition\nFROM sys.sql_modules\nWHERE object_id = OBJECT_ID('trigger_name');\n</code></pre>\n\n<p>Or</p>\n\n<pre><code>SELECT OBJECT_NAME(parent_obj) [table name], \n   NAME [triger name], \n   OBJECT_DEFINITION(id) body\nFROM sysobjects\nWHERE xtype = 'TR'\n  AND name = 'trigger_name';\n</code></pre>\n\n<p>definition using <strong>OBJECT_DEFINITION</strong> function:</p>\n\n<pre><code>SELECT OBJECT_DEFINITION(OBJECT_ID('trigger_name')) AS trigger_definition;\n</code></pre>\n\n<p>definition using <strong>sp_helptext</strong> stored procedure:</p>\n\n<pre><code>EXEC sp_helptext \n 'trigger_name';\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/44034', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/4525/']