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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
You can use Dynamic SQL and the
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]:
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.