copying all triggers from one DB to another in SQL Server 2005

Is there a way in SQL Server 2005 to copy all the triggers to another database with the same schema but one that does not have any of the triggers yet? (DB1 has 50 tables with triggers; DB2 has 50 tables with the same name/schema, but without any triggers, but should have the same as in DB1)

I realize I can go to each trigger in a table and script it out, but that can be time- consuming when there are 50+ tables with triggers.

Who is Participating?
momi_sabagConnect With a Mentor Commented:
you can try either:
1) use the management studio to copy the objects - right click on the database that contains the triggers and search for copy objects or something similar (i don't remember exactly but i know it exists since i've used it). it will open a wizard that will allow you to choose which objects you wish to copy and the destination database.

2) use a script to generate the sql statements that creates the triggers, something like:
declare @ddl table (id int identity(1,1), txt varchar(max))
declare @currTrigger sysname
declare @currID int

select @currid = id, @currTrigger = name
from sys.objects
where type='TR'
order by id

while @@ROWCOUNT > 0
insert into @ddl (txt) execute sp_helptext @currTrigger

select @currid = id, @currTrigger = name
from sys.objects
where type='TR'
and id > @currid
order by id


select txt from @ddl order by id
dportasConnect With a Mentor Commented:
Don't you have a source control system with the scripts already in it?

If not, you can script all the triggers from Management Studio. Right-click the database, choose All Tasks, Generate Scripts. Ensure the Script Triggers option is turned on. Select the table(s) you want. Finally, choose the option to script each object to a separate file. You should now have a script for each of your triggers.

I recommend you save all your database scripts somewhere so that you won't have to do this again.
rob_farleyConnect With a Mentor Commented:
To get all your trigger definitions, you should be able to use:

select m.definition
      sys.triggers t
      sys.sql_modules m
      on t.object_id = m.object_id

Then you can easily execute the results on your other server.

Then do as David suggests and get a decent source control system up and running.

Mark WillsConnect With a Mentor Topic AdvisorCommented:
Just one small recommendation on the above...

either run to an output file rather than a grid (right click on your query window), or run as a BCP command. Reason is to get those carriage return / line feeds correct...

BCP example...

DECLARE @bcpCommand varchar(500)
SET @bcpCommand = 'bcp "select ''-- ==================================='' + char(13) + char(10) + ''-- '' +  + char(13) + char(10) + ''-- ==================================='' + char(13) + char(10) + m.definition + char(13) + char(10) + ''GO'' + char(13) + char(10) from mrwtemp.sys.triggers t join mrwtemp.sys.sql_modules m on t.object_id = m.object_id" queryout "C:\ee\my_triggers.txt" -T -c -CACP'
EXEC master..xp_cmdshell @bcpCommand

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.