[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

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

Posted on 2009-07-11
Medium Priority
203 Views
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
Question by:anushahanna
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 37

Accepted Solution

momi_sabag earned 500 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

dportas earned 500 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 15

Assisted Solution

rob_farley earned 500 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

Mark Wills earned 500 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

0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
###### Suggested Courses
Course of the Month14 days, 22 hours left to enroll