SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Triggers - Script out to file


Triggers - Script out to file

Author
Message
Warren Peace
Warren Peace
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1363 Visits: 476
I am in need of copying triggers from over 100+ tables to a another DB. Droping tables and recreating them is not an option.

Thanks
GSquared
GSquared
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60357 Visits: 9730
How about something like:


select definition
from sys.sql_modules
inner join sys.triggers
on sql_modules.object_id = triggers.object_id



Does that give you what you need?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Warren Peace
Warren Peace
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1363 Visits: 476
Thanks but no.
I may have not been clear on what I want. I need to script out EVERY trigger as like a "Create to..." to a text file so I can run the create scripts on the other DB.

Does that make better sense?

Thanks
GSquared
GSquared
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60357 Visits: 9730
If you want one file per trigger, you can do that from scripting the database. Right-click on the database in Management Studio, select Tasks, Generate Scripts, and then pick the triggers you want to script, and choose one file per object.

If you want a file that will create all your triggers, the query I gave you can do that. Set the output to "File" and run it, or copy-and-paste the results into Notepad, or whatever you want to do to file it. It will give you the create statements for each trigger. You might need to add "GO" between triggers, but that's just a string function.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
GSquared
GSquared
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60357 Visits: 9730
To clarify:

I set the output to Text, created a couple of triggers, and ran the query. Here's what I got:


create trigger Numbers_NoDel on dbo.Numbers
instead of delete
as
rollback;

go
create trigger Numbers_NoUpd on dbo.Numbers
instead of update
as
rollback;

go



Exact query run:


set nocount on

select definition + '
go'
from sys.sql_modules
inner join sys.triggers
on sql_modules.object_id = triggers.object_id



That result is good enough to run on another database and create all my triggers.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Warren Peace
Warren Peace
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1363 Visits: 476
The text formatting is all over the place and I get the error..
Incorrect syntax near 'inserted'.

Most likely because the text is all on a single line.
Any way of changing it to format it correctly where the carriage return should be?
Warren Peace
Warren Peace
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1363 Visits: 476
My fault, I forgot to do outout as text.

OK looks like it may work but the addition of the 'Go' statement isn't there. I'll tweak it a bit...
GSquared
GSquared
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60357 Visits: 9730
Yes. Right-click on the query pane (in Management Studio), select "Results To", and change it to Text, instead of Grid. That's what I meant by setting the output to text. You can do the same thing from the menu or the toolbars, I'm just in the habit of using right-click for it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Warren Peace
Warren Peace
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1363 Visits: 476
No dice. It doesn't look like it is returning the entire trigger script. These triggers have at least a min on 100 lines it them. Your scrip only returns the first 25 or so...

Thanks
Jared Hunt
Jared Hunt
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 2715
By default, I believe results to text only outputs the first 255 characters or so. Go to Tools >> Options, Expand Query Results, Expand SQL Server, and Highlight Results to Text. There you will see the option "Maximum number of characters to display". Set that to like 2000 and you should see everything.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search