Generate Script for all Triggers on a Table?

Question

Generate Script for all Triggers on a Table?

Welsh Corgi

SSC Guru

Points: 116520
More actions
April 7, 2016 at 10:35 am

#399994

How do you generate a Script for all Triggers on a Table?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

SSMS Options-Scripting. Scroll down and triggers is one of the options that is OFF by default. I ALWAYS turn this on, along with indexes and a few others.

After you turn one the things you want, just right-click on a table and script as create to new window or clipboard an VOILA!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

TheSQLGuru (4/7/2016)
SSMS Options-Scripting. Scroll down and triggers is one of the options that is OFF by default. I ALWAYS turn this on, along with indexes and a few others.
After you turn one the things you want, just right-click on a table and script as create to new window or clipboard an VOILA!

Sweet, thank you!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

No worries mate. I sure wish those were on by default!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

jay81 Old Hand Points: 381 More actions · Answer 4

One can view the Trigger definitions by querying syscomments view.

You can sys.objects to get all the triggers.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

jay81 (4/7/2016)
One can view the Trigger definitions by querying syscomments view.
You can sys.objects to get all the triggers.

The system view sys.triggers will work as well.

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 6

Database> Tasks> Generate Scripts > Next > Next

On Choose Script Options UI, under Table/View Options Heading, set Script Triggers to True.

chandrakant_gaurav Right there with Babe Points: 799 More actions · Answer 7

You can get it from system views:

Example:

select

so.name, st.name, sc.text

from sys.triggers st

inner join sysobjects so on st.parent_id = SO.id

inner join syscomments sc on st.object_id = sc.id

where so.name ='<TableName>'

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 8

chandrakant_gaurav wrote:

You can get it from system views:
Example:
select
so.name, st.name, sc.text
from sys.triggers st inner join sysobjects so on st.parent_id = SO.id inner join syscomments sc on st.object_id = sc.id where so.name ='<TableName>'

You posted an answer to a 7 year old post. There's nothing wrong with that... just wanted to let you know because no one from the original post might respond.

Also, consider not using views that have been deprecated since 2005. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)