Enable Disable All Triggers in a Database

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].UDP_EnaDisTrigger') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].UDP_EnaDisTrigger
GO


/***************************************************************************************
Created By :- Vinay Kumar
Created on :- 04 Jan 2008
Purpose :- Enable and disable the all Database Triggers
****************************************************************************************/
SET NOCOUNT ON
GO

create Procedure UDP_EnaDisTrigger @check bit =0 
-- 0 for Disable Triggers
-- 1 for Enable Triggers
-- Default 0
as

if exists (select * from tempdb..sysobjects where name= '##trtemp' and xtype='u')
drop table ##trtemp


declare @maxid int
declare @string varchar(8000)
declare @tbname varchar(100)
declare @trname varchar(100)
Create table ##trtemp (trid int identity(1,1), Tabel_Name varchar(200))
insert into ##trtemp select name as tbname from sysobjects where id in(select parent_obj from sysobjects where xtype='tr')

select @maxid=max(trid) from ##trtemp
while (@maxid>=1)
begin 
    select @tbname=Tabel_name from ##trtemp where trid =@maxid

    if @check=0
        set @string ='Alter table '+ @tbname + ' disable trigger all'
    if @check=1
        set @string ='Alter table '+ @tbname + ' enable trigger all'
exec (@string)
--print @string
    set @maxid = @maxid-1
end

if @check=0
    select '" TRIGGERS ARE DISABLED "'
if @check=1
    select '" TRIGGERS ARE ENABLED "'



--exec udp_enadistrigger 1


SET NOCOUNT OFF
GO