Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a database with 104 triggers, is there a way to delete all the triggers with a single command from a single database called 'system_db_audits?

share|improve this question
up vote 13 down vote accepted

You can use Dynamic SQL and the sys.triggers DMV to build query that you can execute.

is_ms_shipped excludes any triggers that were shipped with SQL Server.
parent_class_desc filters for object level triggers, rather than database level.

Change the PRINT to an EXEC once you are happy with the output.

USE system_db_audits;
GO

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += 
    N'DROP TRIGGER ' + 
    QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + N'.' + 
    QUOTENAME(t.name) + N'; ' + NCHAR(13)
FROM sys.triggers AS t
WHERE t.is_ms_shipped = 0
  AND t.parent_class_desc = N'OBJECT_OR_COLUMN';

PRINT @sql;
share|improve this answer

Use Sys.Triggers Meta Data Table which contains a row for each object that is a trigger

  1. Change output mode to text by clicking the toolbar button shown here:

enter image description here

  1. Execute this script:

    USE YourDBName
    GO
    SELECT ' GO ' + Char(10) + Char(13) + 'DROP TRIGGER ' 
        + QUOTENAME(OBJECT_SCHEMA_NAME(O.[object_id])) + '.' 
        + QUOTENAME(name)
    FROM sys.sql_modules as M 
        INNER JOIN sys.triggers as O 
            ON M.object_id = O.object_id; 
    
  2. Copy Output into a new SQL Server Management Studio window, verify the code performs the actions you are expecting, and Execute.

share|improve this answer
    
Don't the DROP TRIGGER statements need terminators ; though? – ypercubeᵀᴹ Jan 8 at 12:34
    
MSDN says: Transact-SQL statement terminator.Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version. – AA.SC Jan 8 at 12:50

In case you want to run a sql job on a central server [ServerA] to do the trigger deletion work, I'll provide a PowerShell version assuming you have a SQL Server 2012 (or above) instance with SQLPS module installed on [ServerA]

Say you want to delete all triggers in [AdventureWorks] database on [ServerB] SQL Server instance (SQL Server 2005+).

You can run the following PS on [ServerA]:

import-module sqlps -DisableNameChecking;
$db=get-item -Path "sqlserver:\sql\ServerB\default\databases\AdventureWorks";

#before deletion, you can check that triggers do exist
$db.tables.triggers | select name

#now delete
$db.tables.triggers |Where-Object {-not $_.IsSystemObject } | foreach-object {$_.drop()};

#check after deletion
$db.tables.triggers | select name;

Please remember to replace ServerB and AdventureWorks with your own values.

This is pretty flexible solution which you can easily customize to adapt to other different requirements, such as only delete triggers belong to a specific set of tables, or disable (instead of delete) some specific triggers etc.

Strictly speaking, the solutions provided by @Mark Sinkinson is not correct because the requirement is not to delete triggers on 'system_db_audits' db, but to delete triggers in another db from 'system_db_audits'. This means you need to create a dynamic sql in 'system_db_audits' to wrap the "dynamic sql" provided by @Mark Sinkinson to delete those target triggers assuming that both 'system_db_audits' and the target db are on the same sql server instance. Otherwise if the two dbs are not on the same instance, it will be even much "ugly" to handle the deletion (such as via linked server etc). In such scenario, PS is an elegant solution no matter where the target db is or is not on the same sql instance.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.