Tuesday, June 26, 2007

Trigger's definition

I have had a clinet who wanted to script out the source of triggers on database level, so I'm talking about SQL Server 2005. Well as you probably know you can easily script the objects by using Tasks ---> Generate Scripts.. and moreover if you installed SP2 you will be able to script the object per file.

I would like to show you more simple way. You join two system tables on object_id column where sys.sql_modules table has a definition column which my client wanted to get.The WHERE condition restricts the ouptupt for triggers that were designed on database level.

SELECT definition FROM sys.sql_modules sm
INNER JOIN sys.triggers st ON
WHERE parent_class_desc ='DATABASE'

1 comment:

aiya said...
This comment has been removed by a blog administrator.