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.
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 :)