Post reply

trigger ddl export to file/grid

  • July 27, 2010 at 6:39 am

    #224631

    Hi,

    Does anyone know how can i export/query all the triggers' DDLs of certain database to grid/file (Server 2000)?

    (basically to get them into sql file so i could run that sql on another server and reproduce the triggers).

    Best Regards,

    Silver

  • July 27, 2010 at 7:53 am

    #1198710

    this will give you the trigger name and the definition for everything in a given database:

    --SQL 2000:

    select

    sysobjects.name,

    syscomments.text

    from syscomments

    inner join sysobjects on syscomments.id=sysobjects.id

    where sysobjects.xtype='TR'

    --SQL 2005/2008

    select

    object_name(object_id) as TriggerName,

    definition from sys.sql_modules

    where object_id IN(select object_id from sys.objects where type='TR')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 27, 2010 at 9:09 am

    #1198770

    You can script everything with a right click/Generate script.

    But I think Lowell's method might be the quickest way to do this and easier to edit if you need to.

  • July 28, 2010 at 12:17 am

    #1199078

    Thanks a lot, that was what i was looking for.

    Silver.

  • September 7, 2015 at 9:46 am

    #1825002

    This is the script I use. It retains formatting and won't encounter problems with lines splitting mid-command, plus it's easy to filter by trigger type.

    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[/url] if it doesn't make sense.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply