Solved

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

Posted on 2009-07-11
4
192 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:anushahanna
4 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 125 total points
ID: 24833279
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
 
LVL 22

Assisted Solution

by:dportas
dportas earned 125 total points
ID: 24833709
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
 
LVL 14

Assisted Solution

by:rob_farley
rob_farley earned 125 total points
ID: 24837578
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
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 125 total points
ID: 24840061
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

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now