Find Trigger Status

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.usp_get_trigger_status (
@dbName SYSNAME = null
   ) AS
/************************************************************************************************************************************************************************
** Object Name:dbo.usp_find_trigger_status
** Author: Vaiyapuri Subramanian
** Date: 01/14/2003
** Description: Stored Procedure for finding the status of triggers
** Where Used: Reporting Application
** Dependency: N/A
**
** Logic: 
** 
** Change Log: 
**                      
** WHOWHENWHY?
**
*****************************************************************************************************************************************************************************/BEGIN
-- Set Nocount ON
SET NOCOUNT ON

-- use the current db if the @dbName is not specified
IF @dbname IS NULL SELECT @dbname = DB_NAME()

EXEC(
'USE ' + @dbname + 
'-- Variable Declaration
DECLARE @triggerName VARCHAR(255)
-- Declare cursor to get trigger name
DECLARE triggerCursor CURSOR FOR 
SELECT [name] FROM sysobjects a JOIN syscomments b ON a.id = b.id WHERE type = ''TR''

-- Create a temp table to hold trigger name and status
CREATE TABLE #trigger_table (
trigger_nameVARCHAR(255),
trigger_statusVARCHAR(10)
)

-- open the cursor
OPEN triggerCursor

-- Fetch the first value into a variable
FETCH NEXT FROM triggerCursor INTO @triggerName

-- Loop thru the cursor and insert the trigger name and status
-- into the temp table
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #trigger_table
SELECT 
@triggerName,
CASE OBJECTPROPERTY(OBJECT_ID(@triggerName), ''ExecIsTriggerDisabled'')
 WHEN 1 THEN ''Disabled''
WHEN 0 THEN ''Enabled''
ELSE ''Trigger not found'' 
END AS ''Trigger status''
FETCH NEXT FROM triggerCursor INTO @triggerName
END

-- Close and Deallocate the cursor
CLOSE triggerCursor
DEALLOCATE triggerCursor

-- Select all the trigger name and status
SELECT 
trigger_name,
trigger_status
FROM
#trigger_table

-- Drop the temp table
DROP TABLE #trigger_table'
)

-- Set nocount off
SET NOCOUNT OFF
END
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO