Generate a script for triggers only using the script wizard

Question

I have SQL Server 2008 R2. I have about 150 tables in the database, and for each table I recently created triggers. It works great in my local environment.

Now I want to deploy them in my living environment. The question is that I want to deploy only triggers. I tried the Generate Script wizard , but it creates a table schema script along with triggers, not just triggers.

In any case, to generate all triggers and create a script type?

Answer 1

Forget the master. I think you should get your hands dirty with code. The script below prints all the trigger code and saves it in a table. Just copy Script 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