Copy triggers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Copy triggers

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2011-03-21 : 23:19:57

I want to copy all of the triggers from one database to another. It's too many tables to trace and copy one by one.
Is there a way ?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-21 : 23:49:20

Here's a quick and dirty way that comes to mind...

In SSMS, set the output to text. Then execute this:

Declare @trig sysname

Declare c Cursor
Read_Only
For select name from sys.triggers
Open c
Fetch Next From c into @trig
WHILE @@fetch_status = 0
BEGIN
	EXEC sp_helptext @trig
	PRINT 'GO'
	Fetch Next From c into @trig
END
Close c
Deallocate c

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-22 : 04:28:56

or use tr in place of p and fn
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/13/script-out-procedures-and-functions-part-2.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources