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: 
**                      
** WHO				WHEN						WHY?
**
*****************************************************************************************************************************************************************************/
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_name	VARCHAR(255),
				trigger_status	VARCHAR(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