How do I list (or export) the code for all triggers in a database?

Question

I'm changing from local time to UTC-time in our database.
There are alot of triggers that copies information to history tables that currently uses GETDATE().

I would like to find every trigger that uses GETDATE() (instead of GETUTCDATE()) in the database, is there any way to do this automatic?

I've listed them by select * from sys.triggers but I also need to see the actual code to be able to find the use of GETDATE().

Answer 1

Your could try the following:

SELECT      o.[name],
            c.[text]
FROM        sys.objects AS o
INNER JOIN  sys.syscomments AS c
ON      o.object_id = c.id
WHERE   o.[type] = 'TR'

Answer 2

Here's the script I used to export triggers:

DECLARE @t VARCHAR (MAX)
SET @t = ''
SELECT @t = @t + 'IF EXISTS (SELECT 1 FROM sys.triggers WHERE object_id = OBJECT_ID(N''' + s.name + '.' + o.name +'''))
DROP TRIGGER ' + s.name + '.' + o.name + '
GO
' + OBJECT_DEFINITION (OBJECT_ID( s.name + '.' + o.name )) +'
GO
'
FROM sys.objects o
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.objects o2 ON o.parent_object_id = o2.object_id
WHERE o. [type] = 'TR'
AND (
OBJECTPROPERTY ( o.object_id , 'ExecIsInsertTrigger' ) = 1
OR
OBJECTPROPERTY ( o.object_id , 'ExecIsUpdateTrigger' ) = 1
OR
OBJECTPROPERTY ( o.object_id , 'ExecIsDeleteTrigger' ) = 1
)
SELECT @t AS [processing-instruction(x)] FOR XML PATH ('')

More details here if it doesn't make sense to anyone:

http://paulfentonsql.co.uk/2015/09/01/generate-createdrop-statements-for-all-triggers-of-a-given-type/