Samuele Locatelli
4 lines
1.2 KiB
Transact-SQL
4 lines
1.2 KiB
Transact-SQL
CREATE
|
|
PROCEDURE dbo.usp_get_trigger_status ( @dbName SYSNAME = null ) AS BEGIN 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' ) END
|
|
|