Solved

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

Posted on 2009-07-11
4
201 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
[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
  • Learn & ask questions
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 15

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

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

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

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 …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

724 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