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?
Database-> Tasks-> Generate Scripts -> Next -> NextOn Choose Script Options UI, under Table/View Options Heading, set Script Triggers to True.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With