-----------------------------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
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
Comments
Post a Comment