Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Generate script for triggers only using script wizard

I have SQL Server 2008 R2. I have around 150 tables in a database and for each table I have recently created triggers. It is working fine in my local environment.

Now I want to deploy them on my live environment. The question is I want to deploy only the triggers. I tried the Generate Script wizard but it is creating script with table schema along with triggers, NOT triggers only.

Is there anyway to generate all the triggers drop and create type script?

like image 893
Dev Avatar asked Jan 19 '13 10:01

Dev


People also ask

How do I create a script for just triggers in SQL Server?

Database-> Tasks-> Generate Scripts -> Next -> NextOn Choose Script Options UI, under Table/View Options Heading, set Script Triggers to True.

How do you generate create table script for all tables in SQL Server database?

Right click on the DB_NAME -> Select Task -> Select Generate Script. Follow along the presented wizard and select all tables in that database to generate the scripts.


1 Answers

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
like image 63
pkmiec Avatar answered Oct 11 '22 22:10

pkmiec