Script of DDL triggers

 

How do I
view the script of the DDL triggers?

The answer is not

sp_helptext 'DDL trigger'

Because DDL triggers are defined at database/server level and not at any table level
So they are not stored in a system objects like sys.objects and the usage of sp_helptext,object_name, etc wont work

Using this

select * from sys.objects where type='tr'

You can see all triggers except DDL triggers

To know the DDL trigger, you need to use one of the following

select * from sys.triggers
where parent_class=0

select m.* from sys.sql_modules as m left join sys.objects as o
on m.object_id=o.object_id
where o.object_id is null

To script the DDL trigger, you need to use one of the following

select object_definition(object_id) from sys.triggers
where parent_class=0

select m.definition from sys.sql_modules as m left join sys.objects as o
on m.object_id=o.object_id
where o.object_id is null

Published Thursday, December 11, 2008 1:28 PM by Madhivanan

Comments

# Less Than Dot - Blog - Awesome

Sunday, January 12, 2014 3:22 PM by Less Than Dot - Blog - Awesome

Pingback from  Less Than Dot - Blog - Awesome