Click here to Skip to main content
Click here to Skip to main content

Drop all Triggers belonging to any schema in MS SQL Server

, 9 Oct 2013
Rate this:
Please Sign up or sign in to vote.
How to remove Microsoft SQL Server triggers associated with a custom schema.

Background 

After migrating MS Access tables to MS SQL Server, the process generated unwanted or unnecessary triggers. Once you attempt to resign or save data to the affected tables any associated triggers is fired off. This behavior results in seamless errors and inconvenience. In order to remove all the triggers from the database, after some where created with schema DBO and other custom schema, it became necessary to generate or write scripts to remove hundreds of these triggers. 

Using the code

Run the following code within the database where the triggers must be removed. 

DECLARE @SQLCmd nvarchar(1000) 
DECLARE @Trig varchar(500)
DECLARE @sch varchar(500)

DECLARE TGCursor CURSOR FOR

SELECT ISNULL(tbl.name, vue.name) AS [schemaName]
     , trg.name AS triggerName
FROM sys.triggers trg
LEFT OUTER JOIN (SELECT tparent.object_id, ts.name 
                 FROM sys.tables tparent 
                 INNER JOIN sys.schemas ts ON TS.schema_id = tparent.SCHEMA_ID) 
                 AS tbl ON tbl.OBJECT_ID = trg.parent_id
LEFT OUTER JOIN (SELECT vparent.object_id, vs.name 
                 FROM sys.views vparent 
                 INNER JOIN sys.schemas vs ON vs.schema_id = vparent.SCHEMA_ID) 
                 AS vue ON vue.OBJECT_ID = trg.parent_id
 
OPEN TGCursor
FETCH NEXT FROM TGCursor INTO @sch,@Trig
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQLCmd = N'DROP TRIGGER [' + @sch + '].[' + @Trig + ']'
EXEC sp_executesql @SQLCmd
PRINT @SQLCmd

FETCH next FROM TGCursor INTO @sch,@Trig
END

CLOSE TGCursor
DEALLOCATE TGCursor

Points of Interest

The interesting thing here is that the datatype sysname could not be used to stored the trigger or the schema return type. Also, owner is not the schema name.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Matthew Taylor
Software Developer Cox Telecommunication
United States United States
15+ years Information System and Software Development experience.
Currently Senior Software Engineer @ Cox Telecommunication Hamptons Roads.
 
Qualification:
1.Master of Science Electronic Commerce Development.
2.Microsoft Certified Application Developer (MCAD)
3.Brain Bench Certified .Net Developer
 
Currently persuiting my PMP from PMI.org. Should be taking the exam in 2 months.

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web01 | 2.8.140821.2 | Last Updated 9 Oct 2013
Article Copyright 2013 by Matthew Taylor
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid