You are here:Home » tsql » List ALL DDL Triggers with database scope and server scope

List ALL DDL Triggers with database scope and server scope

-----------------------------Databasae level scope-------------------------

use master
go
create table ddllist
(
dbname varchar(200),
trname varchar(200)
)

declare @dbname varchar(500)
declare @sql varchar(8000)
declare dbcur CURSOR LOCAL FAST_FORWARD
for
select name from sys.sysdatabases
open dbcur
fetch next from dbcur into @dbname
while @@fetch_status = 0
begin
set @sql = 'insert into master..ddllist select '+''''+@dbname+''''+',name from '+@dbname+'.sys.triggers where name like ' +''''+'%ObjectName%'+''''
print @dbname
exec (@sql)
fetch next from dbcur into @dbname
end
close dbcur
deallocate dbcur

select * from master..ddllist


--disable trigger triggername on database
--enable trigger triggername on database
--drop trigger triggername on database

-----------------------------Server level scope-------------------------


select * from sys.server_triggers

--disable trigger triggername on all server
--enable trigger triggername on all server
--drop trigger triggername on all server