Script Just Triggers, SQL 2005

[yeagly21]

Does anyone know how to script just triggers out of a SQL 2005 database?

 

[AlexCuse]

This should give you some ideas. You will just need to change your criteria to script the triggers rather than the procs. Not sure what the type is off hand, but should be easy enough to find out.

thread183-1354984

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ptheriault]

In 2005 there are 3 levels of triggers, table, database and server. Which level do you want to script?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[yeagly21]

Paul -

I am looking to script the Table triggers. Sorry I should have included that info.

 

[AlexCuse]

Yeagly21 - did you look at the thread that I posted?

Ignorance of certain subjects is a great part of wisdom

 

[yeagly21]

I am looking at it right now to see if I can adjust the script to give me that results i am looking for.

 

[yeagly21]

This is what I ended up creating to give me the result set. I ran it in Management Studio and ran the query to text. Its not the most efficient method so if anyone has a better way to do this that would be great to know.

DECLARE @ProcDefinition VARCHAR(MAX),@ProcName VARCHAR(8000)
Declare Load_curs cursor for

select  name as 'Trigger' as 'Name'
from sysobjects
where xtype = 'TR'

open Load_curs
fetch next from Load_curs into @ProcName 

while (@@fetch_status=0)
begin

PRINT 'SET ANSI_NULLS ON '
PRINT 'GO'
PRINT 'SET QUOTED_IDENTIFIER ON'
PRINT 'GO'
 
EXEC  sp_HelpText @ProcName 

PRINT 'GO'

fetch next from Load_curs into @ProcName
end
close Load_curs
deallocate Load_curs

-Yeagly21

 

[ESquared]

sp_HelpText has weird problems with white space, at least in my experience. Be wary.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]