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.

Thanks
LVL 6
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
begin
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

end

select txt from @ddl order by id
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

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.
0
Commented:
To get all your trigger definitions, you should be able to use:

select m.definition
from
sys.triggers t
join
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.

Rob
0
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) + ''-- '' + t.name  + 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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.