How to export triggers from a database?

I would like to generate a script/extract all of the triggers I hav开发者_JAVA百科e in database, so that I can use them in another one. How can I do this?

You can get the list of triggers from sys.all_objects (sysobjects on 2000) and then the code from sp_helptext procedure.

Example: http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-show-all-the-triggers-in-a-database.html

You can find trigger objects either on sys.objects view with an xtype = 'tr' or, starting in SQL 2008, on the sys.triggers view.

You can get the definition for triggers (or any other object) by joining to sys.sql_modules.definition or by calling OBJECT_DEFINITION(object_id).
_{Just don't use sys.syscomments.text because it caps out at nvarchar(4000)}

In any combination of the above (really doesn't matter) you could generate a single file to populate all of your scripts by terminating each definition with "Go" like this:

SELECT m.definition, 'GO'
FROM sys.triggers tr
JOIN sys.sql_modules m ON tr.object_id = m.object_id

Just take the resulting output and save it in a single file and execute to recreate every trigger.

Generate Individual Scripts

The problem is a little more complex if you want to generate individual files for each script.
^{...mostly in automating the file generation; the previous solution leans on just presenting data and letting you save it.}

You can use bcp utility, but I find SQL to be pretty clumsy at dealing with generating files. Alternatively, just grab the data in powershell and then generate files from there where you have a lot more fine tuned access and control. Here's a script that will grab all the triggers and create them into folders for each table.

# config
$server = "serverName"
$database = "dbName"
$dirPath = 'C:\triggers'

# query
$query = @"
SELECT TOP 5
       t.name AS TableName, 
       tr.name AS TriggerName,
       m.definition As TriggerScript
FROM sys.triggers tr
    JOIN sys.tables t ON tr.parent_id = t.object_id
    JOIN sys.sql_modules m ON tr.object_id = m.object_id
"@

# connection
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
$sqlConnection.Open()

# command
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand($query, $sqlConnection)
$sqlCmd.CommandTimeout = 10 * 60 # 10 minutes


# execute
$reader = $sqlCmd.ExecuteReader()
while ($reader.Read())
{
    # get reader values
    $tableName = $reader.GetValue(0)
    $triggerName = $reader.GetValue(1)
    $triggerScript = $reader.GetValue(2)

    $filepath = "$dirPath\$tableName\$triggerName.sql"

    # ensure  directory exists
    [System.IO.Directory]::CreateDirectory("$dirPath\$tableName\")

    # write file
    New-Item -Path $filepath -Value $triggerScript -ItemType File
}
$reader.Close()
$sqlConnection.Close()