How to Generate Scripts For All Triggers in Database Using Microsoft SQL Server Management Studio

Database-> Tasks-> Generate Scripts -> Next -> Next

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

enter image description here

enter image description here


I know the answer has been accepted already, but want to provide another solution for cases when for some reason SSMS wizard is not able to generate script for triggers (in my case it was MSSQL2008R2)

This solution is based on idea from dana above, but uses 'sql_modules' instead to provide the full code of the trigger if it exceeds 4000 chars (restriction of 'text' column of 'syscomments' view)

select [definition],'GO' from sys.sql_modules m
inner join sys.objects obj on obj.object_id=m.object_id 
 where obj.type ='TR'

Right click on the results grid and then "Save results as..." saves to file with formatting preserved


How about this?

select text from syscomments where text like '%CREATE TRIGGER%'

EDIT - per jj's comment below, syscomments is deprecated and will be removed in the future. Please use either the wizard-based or script-based solutions listed above moving forward :)