How to Generate Scripts for All Triggers in Database Using Microsoft SQL Server Management Studio

How to Generate Scripts For All Triggers in Database Using Microsoft SQL Server Management Studio

Database-> Tasks-> Generate Scripts -> Next -> Next

On Choose Script Options UI, under Table/View Options Heading, set Script Triggers to True.

Generate script for triggers only using script wizard

Forget the wizard. I think you have to get your hands dirty with code. Script below prints all triggers code and stores it into table. Just copy the script's print output or get it from #triggerFullText.

USE YourDatabaseName
GO
SET NOCOUNT ON;

CREATE TABLE #triggerFullText ([TriggerName] VARCHAR(500), [Text] VARCHAR(MAX))
CREATE TABLE #triggerLines ([Text] VARCHAR(MAX))

DECLARE @triggerName VARCHAR(500)
DECLARE @fullText VARCHAR(MAX)

SELECT @triggerName = MIN(name)
FROM sys.triggers

WHILE @triggerName IS NOT NULL
BEGIN
    INSERT INTO #triggerLines 
    EXEC sp_helptext @triggerName

    --sp_helptext gives us one row per trigger line
    --here we join lines into one variable
    SELECT @fullText = ISNULL(@fullText, '') + CHAR(10) + [TEXT]
    FROM #triggerLines

    --adding "GO" for ease of copy paste execution
    SET @fullText = @fullText + CHAR(10) + 'GO' + CHAR(10)

    PRINT @fullText

    --accumulating result for future manipulations
    INSERT INTO #triggerFullText([TriggerName], [Text])
    VALUES(@triggerName, @fullText)

    --iterating over next trigger
    SELECT @triggerName = MIN(name)
    FROM sys.triggers
    WHERE name > @triggerName

    SET @fullText = NULL

    TRUNCATE TABLE #triggerLines
END

DROP TABLE #triggerFullText
DROP TABLE #triggerLines

Is there a way to (task) generate scripts from query window in Microsoft SQL Server Management Studio?

After some more research, and even though I did not find exactly what I was looking for, I managed to find something that covered my needs, thanks to this posts:
- Generate SQL server scripts from command line?
- Script entire database SQL-Server
- https://dba.stackexchange.com/questions/56616/sql-server-2012-generate-scripts-from-command-line

From the varied options the answers in them offer, I ended up using Schemazen (https://github.com/sethreno/schemazen), that did what I needed quick and easy.

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

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'

Alter All Triggers via T-SQL

You can get all trigger using

SELECT [name] FROM [sys].[triggers]

and loop through each trigger and execute

EXEC sp_helptext 'TriggerName'

so you will have the create statement with it...

Generate script in SQL Server Management Studio

Here what you have to do:

1. right click the database (not the table) and select tasks --> generate scripts
2. Next --> select the requested table/tables (from select specific database objects)
3. next --> click advanced --> types of data to script = schema and data
4. next and finish

How can I automate the generate scripts task in SQL Server Management Studio 2008?

What Brann is mentioning from the Visual Studio 2008 SP1 Team Suite is version 1.4 of the Database Publishing Wizard. It's installed with sql server 2008 (maybe only professional?) to \Program Files\Microsoft SQL Server\90\Tools\Publishing\1.4. The VS call from server explorer is simply calling this. You can achieve the same functionality via the command line like:

sqlpubwiz help script

I don't know if v1.4 has the same troubles that v1.1 did (users are converted to roles, constraints are not created in the right order), but it is not a solution for me because it doesn't script objects to different files like the Tasks->Generate Scripts option in SSMS does. I'm currently using a modified version of Scriptio (uses the MS SMO API) to act as an improved replacement for the database publishing wizard (sqlpubwiz.exe). It's not currently scriptable from the command line, I might add that contribution in the future.

Scriptio was originally posted on Bill Graziano's blog, but has subsequently been released to CodePlex by Bill and updated by others. Read the discussion to see how to compile for use with SQL Server 2008.

http://scriptio.codeplex.com/

EDIT: I've since started using RedGate's SQL Compare product to do this. It's a very nice replacement for all that sql publishing wizard should have been. You choose a database, backup, or snapshot as the source, and a folder as the output location and it dumps everything nicely into a folder structure. It happens to be the same format that their other product, SQL Source Control, uses.

Need to list all triggers in SQL Server database with table name and table's schema

Here's one way:

SELECT 
     sysobjects.name AS trigger_name 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 
    ,s.name AS table_schema 
    ,OBJECT_NAME(parent_obj) AS table_name 
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects 

INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 

INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 

WHERE sysobjects.type = 'TR' 

EDIT:
Commented out join to sysusers for query to work on AdventureWorks2008.

SELECT 
     sysobjects.name AS trigger_name 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 
    ,s.name AS table_schema 
    ,OBJECT_NAME(parent_obj) AS table_name 
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects 
/*
INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 
*/  
INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 
WHERE sysobjects.type = 'TR' 

EDIT 2: For SQL 2000

SELECT 
     o.name AS trigger_name 
    ,'x' AS trigger_owner 
    /*USER_NAME(o.uid)*/ 
    ,s.name AS table_schema 
    ,OBJECT_NAME(o.parent_obj) AS table_name 
    ,OBJECTPROPERTY(o.id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY(o.id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY(o.id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY(o.id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY(o.id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(o.id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects AS o 
/*
INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 
*/  
INNER JOIN sysobjects AS o2 
    ON o.parent_obj = o2.id 

INNER JOIN sysusers AS s 
    ON o2.uid = s.uid 

WHERE o.type = 'TR'

---

Related Topics