MSSQL Drop All Triggers With Specific PREFIX

Hello.  We need to DROP "All" Triggers in an MSSQL-2012 database, with a "PREFIX" of (for example) 'PDD_'.  Can someone please provide a Dynamic SQL sample script, of how to achieve this, real simple?  We'd appreciate it...Thanks!...Mark
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
Well, you could use sys.triggers
declare @sql varchar(max)

;with drop_triggers as
(select object_name(parent_id) as [Table_name],object_name(object_id) as [Trigger_Name] 
 from sys.triggers where object_name(object_id) like 'PDD_%')                                                                   -- change accordingly
select @sql=isnull(@sql,'')+'drop trigger '+trigger_name+char(13)+char(10)  --,table_name,trigger_name
from drop_triggers

print @sql

If len(@sql) > 6 
   exec(@sql)            -- only execute if / when happy with print

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
sys.triggers returns both DDL (database) and DML (table or view). If only for DML triggers (table or view), you can add 'where parent_class = 1'

my previous post will drop 'All' triggers in a db with that prefix...

strongly suggest a backup before you start so you can lock it away - just in case.

have a look at :
datatechcorpAuthor Commented:
Hi Mark W...

Thank you *sooo* much for responding!  I admit it freely...when it comes to "system" object scripting such as this, I'm very much a 'newbie'.

So....let's suppose...that my database name is, "MyDatabase"...running on a Microsoft 2012 Instance "My2012Server\My2012Instance".  Based on this, precisely, what would your syntax above, look like, knowing that we truly want do *eradicate* any/all "Triggers" from "MyDatabase", where left(Trigger_name,4) = 'PDD_' ?  

(a silly example, I know, but there are, literally, hundreds of such triggers, on ALL tables in the db, and we simply want to KILL them, but leave all others, intact...make sense?).

Please let me know asap...I'd truly appreciate it :-)...

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark WillsTopic AdvisorCommented:
Yep, makes sense... And nothing is a silly example so long as it portrays what you need to achieve :)

simply put
USE  [MyDatabase]

declare .... etc

Open in new window

at the very beginning  above/before the declare
datatechcorpAuthor Commented:
Hi Mark W...

OH...EMM...GEE...what a dope I am!  Thank you *sooooo* much, for pointing out the obvious.  My 'db' choice had been on 'Master'...and no wonder, your script wasn't executing.  This script, in its entirety, is brilliant!  Putting it in the warchest.

Again, thank you soooo much...I appreciate it my friend!...Thanks!...Mark
Mark WillsTopic AdvisorCommented:
A pleasure and very pleased to hear it is working for you :)

Mark WillsTopic AdvisorCommented:
Is there anything else I can help you with before you close this question ?

(and if more questions, can always post a forwarding link in this thread)
datatechcorpAuthor Commented:
Nope, not at this moment.  Again, most appreciated!...Thanks!...Mark
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.