Monday, June 11, 2012

MS SQL | Enable/Disable All Database Triggers on a given Server

Problem:
A simple script that capable of enabling or disabling all databases triggers on a given Microsoft SQL Servers

Solution:
 DECLARE @Status VARCHAR(20)  
 -- Switch to enable/disable:   
 SET @Status = 'DISABLE'  
 -- SET @Status = 'ENABLE'  
 CREATE TABLE #tempTriggerToBeDeleted (RowId int IDENTITY(1,1), SqlTrigger VARCHAR(255))  
 DECLARE @dbName VARCHAR(255)  
 DECLARE @strSQL NVARCHAR(2000)  
 DECLARE ListDbs CURSOR  
 -- Insert into a temp database table:  
 FOR  
      SELECT name FROM master..sysdatabases       
 OPEN ListDbs  
 FETCH NEXT  
      FROM ListDbs  
      INTO @dbName  
 WHILE @@fetch_status = 0  
 BEGIN  
 SELECT @strSQL =   
 '  
      USE ' + @dbname + '  
      SELECT db_name()  
      INSERT INTO #tempTriggerToBeDeleted  
      SELECT   
           ''ALTER TABLE ' + @dbname + ''' + ''.'' + SU.name + ''.'' + SO2.[name] + '' '+ @Status +' TRIGGER '' + SO1.[name] AS DisableTrigger   
      FROM sysobjects SO1   
      JOIN sysobjects SO2 ON SO1.parent_obj = SO2.[id]   
      INNER JOIN syscomments Comments On SO1.id = Comments.id  
      INNER JOIN sysusers SU ON SO1.uid = SU.uid  
      WHERE SO1.xtype=''TR''  
 '  
 EXEC sp_executesql @strSQL  
 PRINT db_name()  
 FETCH NEXT  
      FROM ListDbs  
      INTO @dbName  
 END   
 CLOSE ListDbs  
 DEALLOCATE ListDbs  
 SELECT * FROM #tempTriggerToBeDeleted  
 DECLARE   
      @EndCount INT,  
      @CurrentCount INT  
 SET @EndCount = 0  
 SET @CurrentCount = 1  
 SELECT @EndCount = COUNT(RowId) FROM #tempTriggerToBeDeleted  
 WHILE @CurrentCount <= @EndCount  
 BEGIN  
      DECLARE @SqlTrigger NVARCHAR(2000)  
      SET @SqlTrigger = ''  
      SELECT TOP 1 @SqlTrigger = SqlTrigger FROM #tempTriggerToBeDeleted WHERE RowId = @CurrentCount  
      PRINT @SqlTrigger       
      EXEC sp_executeSQL @SqlTrigger  
      SET @CurrentCount = @CurrentCount + 1  
 END        
 DROP TABLE #tempTriggerToBeDeleted  
 -- Check Triggers Status on current Database:  
 /*  
 SELECT   
      T.[name] as TableName,   
      TR.[Name] as TriggerName,   
      CASE WHEN 1=OBJECTPROPERTY(TR.[id], 'ExecIsTriggerDisabled')THEN 'Disabled' ELSE 'Enabled' END AS Status   
 FROM   
      sysobjects T   
      INNER JOIN sysobjects TR on t.[ID] = TR.parent_obj   
 WHERE   
      (T.xtype = 'U' or T.XType = 'V')   
      AND (TR.xtype = 'TR') ORDER BY T.[name], TR.[name]   
 */  

No comments:

Post a Comment