Checks Triggers status on User- Databases.

use master
go

Create proc [dbo].[P_ZDBA_TRIGGER_STATUS_CHK]  @fpath varchar(255),@Email_address Varchar(50)
as
--*************************************************************************************************************************************                  
-- Date Created/User:06/24/2002 By Wali A. Ali .                                                
--
--SQL Server 7.0 introduced a new class of functions that return a wide range of property information 
--that isn't readily available in SQL Server 6.5 and earlier versions. 
--These functions have three general classes: TypeProperty, which returns information about a data type; 
--ObjectProperty, which returns information about objects in the current database; and 
--ColumnProperty, which returns information about a column or procedure parameter. 
--Together, these classes include dozens of property values you can query for database objects. 
--Base on these power full class of function. This stored procedure is written to search through user databases and 
--identify all disabled triggers in a particular database:
-- -------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- The procedure is located in Database Mgt\Client\DBA Maintenance Utilities                   
--                                                                                              
-- Utility Name:       P_ZDBA_TRIGGER_STATUS_CHK                                                
--                                                                                              
-- Category:         General T-SQL, Stored Procedure                                            
--                                                                                              
-- Utility Location: DBAMNT database on each Server.                                        
--                                                                                              
-- Execution description or syntax:  P_ZDBA_TRIGGER_STATUS_CHK  'f:\apps\dat\','SQLDBA'         
--                                                                                               
-- Result:           A Log file for each database in the following format is written;           
--                       [yyyymmddhhmm}_{Server-name}_TRGCHK.log]                       
--                                                                                               
-- Audience:         DBA                                                                           
--                                                                                              
-- Date Created: June, 2002                                                                 
--
-- Updates/Changes: Updated By      Reason
--------------------------------    -----------------      --------------------------------------------------------------------------------------------------------------
-- 6/24/02                       Wali. Ali          Original                                     
--------------------------------    -----------------      --------------------------------------------------------------------------------------------------------------             
-- Input Parameters:@PATH    varchar [Location where the TRIGGER CHECK log file will be written]
--
--@Email_address   varchar [Email address for the SQL DBAs. The 
--                                       procedure will send out e-mails to all DBAs when there is a disabled TRIGGER found for one or more  
--                                       user database.                  
--
-- Output Parameters:None
--
-- Called By:Normally invoked by a scheduled SQL Agent Job.                                                                     
--
-- Description/(Purpose): This stored procedure is written to search through user databases and 
--                                   identify all disabled triggers in a particular database:
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Step by Step details:    
--
--    1:  Old TRIGER  log files for each database is removed, before the routine is started. 
--
--    2: All system databases are bypassed. [master, tempdb, model, msdb, pubs, northwind, distribution ]                                                                                               
--
--    3: The Log file showing the trigger status--[0 normal and 1 disabled trigger] is generated regardless. 
--                         
--    4:  When one or more trigger with status of 1 is found, the row(s)  is copied to a static work user table, 
--         and E-mail is composed to all SQL-DBA.  
--    
--  **************************************************************************************************************************************
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
SET ANSI_DEFAULTS OFF

Declare @command         as varchar(500),
            @sqlstmt             as varchar(500),
            @temptime          as varchar(21),
            @path                 as  varchar(500),       
            @SQLSTR          as varchar(500)

-- Step1: Delete  old log file
declare @delstmt as varchar(200)
set       @delstmt =  ' Del /q '  +  @fpath + '*_TRGCHK.log'
exec    @sqlstmt = master..xp_cmdshell  @delstmt

        
-- Create new log file-- [yyyymmddhhmm}_{Server-name}_TRGCHK.log]
SET @temptime=CONVERT(CHAR(21),GETDATE(),108)    -- 108 style
SET @temptime=SUBSTRING(@temptime,1,2)+SUBSTRING(@temptime,4,2)
--select @temptime --debugging
SET  @temptime=convert(char(8),getdate(),112)+@temptime  --112 style
-- select @temptime --debugging
SET  @path = @fpath  
SET  @path = @path  + @temptime  
SET  @path = @path + '_'
SET  @path = @path + '_' +@@servername+ '_' + '_TRGCHK.log'
select @path

-- drop and re-create global temp and work table.
if exists (select 1  from INFORMATION_SCHEMA.TABLES where table_schema = 'dbo' and table_name = 'disabled' and table_type = 'BASE TABLE')
  drop table disabled
if exists (select 1  from tempdb.INFORMATION_SCHEMA.TABLES where table_schema = 'dbo' and table_name like '##triggers' and table_type = 'BASE TABLE')
  drop table ##triggers

Begin
create table ##triggers (DB_Name sysname,  Trigger_Status int, Trigger_Name varchar(40),  Create_date datetime ) 
create table DBAMNT.dbo.disabled (DB_Name varchar(30),  Disabled_Trigger_Status int, Trigger_Name varchar(40),  Create_date datetime) 
End

exec sp_MSforeachdb '  USE ? insert into ##triggers SELECT "?" as ''DB-NAME'',OBJECTPROPERTY(object_id(obj.name),''ExecIsTriggerDisabled'') As ''Enable(0), DISable(1)'',right(obj.name,35) as ''Trigger Name'',
obj.crdate  as ''Create_Date'' FROM ?..sysobjects obj, master.dbo.sysdatabases db WHERE type = ''tr'' and db.name = ''?'' and db.name not  in (''master'' ,''model'' ,''tempdb'',  ''msdb'',''pubs'',''Northwind'', ''distribution'') and objectproperty(id,''IsMSShipped'') = 0 '

-- Step2: Write the contents of #triggers out to Trigger log.
SET @SQLSTR = '''BCP "SELECT right(DB_Name,30) as DB_Name, cast(Trigger_Status AS CHAR(1)), right(Trigger_Name,40), convert(char(21),create_date,100) from ##triggers " queryout ' + @path+'  -k -c -S'+@@servername+'  -T'''
SET @command  = 'echo * >> '  + @path
set @command    = 'master..xp_cmdshell ' 
set @command    = @command + @SQLSTR 
select @command
exec(@command)

-- check Triggers that might have disabled status--1 for any database.
Begin
insert into [DBAMNT].[dbo].[disabled] 
 select right(DB_Name,30) as DB_Name, Trigger_Status, Trigger_Name, create_date from ##triggers where Trigger_status = 1
end

select * from [DBAMNT].[dbo].[disabled]  -- during debugging.

-- If any trigger found with disabled status--1, notify  DBA unit via SQL-mail and the contents of disabled temp static table is attached.
declare @errcnt as int,
            @rcnt    as int
Begin
set @errcnt = (select count(*) from [DBAMNT].[dbo].[disabled]) 
set @rcnt   = @@rowcount
    if (@errcnt <> 0 and @rcnt <> 0)
      Begin
        SET NOCOUNT on
        SET @command = 'echo * >> ' + @path
        EXEC master.dbo.xp_cmdshell @command,NO_OUTPUT
        SET  @command= 'echo !!!! ERROR-TRIGGER: Disabled Trigger(s) found !!!!  >> '  + @path
        EXEC master.dbo.xp_cmdshell @command,NO_OUTPUT
        set  @sqlstmt = 'ERROR-TRIGGER: Disabled Trigger(s) found'
        set  @sqlstmt = @sqlstmt + ' ON: ' + UPPER(@@servername) 

-- Now Compose the E-mail notification 
Declare @dbname as varchar(30) 
set @dbname  = 'DBAMNT'
 exec master..xp_sendmail 
          @recipients     = @Email_address,
          @dbuse          = @dbname,
          @Query          = ' select * from [DBAMNT].[dbo].[disabled]',
          @message        = 'ERROR-TRIGGER: Disabled Triggers(s) Found, Check the Attachedment.',
          @subject            = @sqlstmt, 
          @attach_results = 'TRUE', @width = 250
       End
     Else
        SET  @command  = 'echo !!!! NO Disabled TRIGGERS are found !!!!  >> '  + @path
        EXEC master.dbo.xp_cmdshell @command,NO_OUTPUT

/* Last thing to do is to remove the disabled temp static table  and one temp table*/if exists (select 1  from DBAMNT.INFORMATION_SCHEMA.TABLES where table_schema = 'dbo' and table_name = 'disabled' and table_type = 'BASE TABLE')
  drop table disabled
if exists (select 1  from tempdb.INFORMATION_SCHEMA.TABLES where table_schema = 'dbo' and table_name like '##triggers' and table_type = 'BASE TABLE')
  drop table ##triggers
END
Return (@@error) --end of P_Z_DBA_TRIGGER_STATUS_CHK

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO