• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 43
  • Last Modified:

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
0
datatechcorp
Asked:
datatechcorp
  • 5
  • 3
1 Solution
 
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

0
 
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 : https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-triggers-transact-sql
0
 
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 :-)...

Thanks!...Mark
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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]
go

declare .... etc

Open in new window

at the very beginning  above/before the declare
0
 
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
0
 
Mark WillsTopic AdvisorCommented:
A pleasure and very pleased to hear it is working for you :)

Cheers,
Mark
1
 
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)
0
 
datatechcorpAuthor Commented:
Nope, not at this moment.  Again, most appreciated!...Thanks!...Mark
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now