Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Triggers - Script out to file Expand / Collapse
Author
Message
Posted Friday, January 23, 2009 1:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:54 PM
Points: 212, Visits: 466
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
Post #642728
Posted Friday, January 23, 2009 1:33 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #642739
Posted Friday, January 23, 2009 1:37 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:54 PM
Points: 212, Visits: 466
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
Post #642747
Posted Friday, January 23, 2009 1:43 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #642753
Posted Friday, January 23, 2009 1:47 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #642758
Posted Friday, January 23, 2009 2:20 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:54 PM
Points: 212, Visits: 466
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?
Post #642782
Posted Friday, January 23, 2009 2:37 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:54 PM
Points: 212, Visits: 466
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...
Post #642797
Posted Friday, January 23, 2009 2:37 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #642798
Posted Friday, January 23, 2009 2:44 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:54 PM
Points: 212, Visits: 466
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
Post #642804
Posted Friday, January 23, 2009 5:42 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 12, 2010 8:52 PM
Points: 51, Visits: 2,715
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.
Post #642857
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse