Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Drop all triggers in a given database


Drop all triggers in a given database

Author
Message
ram4tech
ram4tech
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 122
Hi,

I am trying to drop all the triggers in a user database. How do I create a stored procedure that will accept user database name as parameter and drop all the triggers in that database. I want to create a SP as we have multiple databases where I get a request to drop all the triggers in a given database.

Here is the partial code, can someone let me know if this approach will work?

USE master
go

IF EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].[DropAllTriggers]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[DropAllTriggers]
GO

CREATE PROCEDURE dbo.DropAllTriggers @dbname VARCHAR(255)
AS

DECLARE @SqlCmd VARCHAR(8000)
DECLARE @Trig VARCHAR(1000)

SET @dbname = ltrim(rtrim(@dbname))

DECLARE TGCursor CURSOR FOR
SELECT name FROM sysobjects WHERE type = 'TR'
OPEN TGCursor
FETCH next FROM TGCursor INTO @Trig
WHILE @@FETCH_STATUS = 0
BEGIN


FETCH next FROM TGCursor INTO @Trig
END
CLOSE TGCursor
DEALLOCATE TGCursor

GO



SQLZ
SQLZ
SSC Eights!
SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)

Group: General Forum Members
Points: 874 Visits: 940
Ram,

If you create the procedure in the master database then you don't need to pass the db name through to it because select * from sysobjects will automatically select from the database you're currently logged into.

Note that you also need the owner name to delete the trigger as you cannot assume that the owner will be "dbo".

To execute this, just change the database and run exec sp_DropAllTriggers.

Note that I have commented out the sp_executesql call and I've added a couple of print statements just to verify I'm going to be deleting what I should be deleting.


CREATE PROCEDURE sp_DropAllTriggers
AS


DECLARE @SqlCmd VARCHAR(8000)
declare @Trig sysname
declare @owner sysname
declare @uid int

DECLARE TGCursor CURSOR FOR
SELECT name, uid FROM sysobjects WHERE type = 'TR'
OPEN TGCursor
FETCH next FROM TGCursor INTO @Trig, @uid
WHILE @@FETCH_STATUS = 0
BEGIN

   set @SQLCmd = 'drop trigger [' + user_name(@uid) + '].[' + @Trig + ']'

--exec sp_executesql @SQLCmd

print 'for testing!'
print ''
print @SQLCmd

   
FETCH next FROM TGCursor INTO @Trig, @uid
END
CLOSE TGCursor
DEALLOCATE TGCursor

GO

Karl
source control for SQL Server
ram4tech
ram4tech
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 122
Karl,

"select * from sysobjects will automatically select from the database you're currently logged into.", this helped me a lot.

Thank you very much.
Ram



SQLZ
SQLZ
SSC Eights!
SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)

Group: General Forum Members
Points: 874 Visits: 940
Yep. That only applies for procedures that are created in master and prefixed with sp_

When executed from a database other than master the procedure runs within the context of that database.

Karl
source control for SQL Server
ram4tech
ram4tech
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 122
Here is the completed stored procedure. I had problems with @SQLCmd variable type being VARCHAR, so changed it to NVARCHAR.

USE master
go

IF EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].[sp_DropAllTriggers]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_DropAllTriggers]
GO

CREATE PROCEDURE dbo.sp_DropAllTriggers
AS

DECLARE @SQLCmd nvarchar(1000)
DECLARE @Trig sysname
DECLARE @owner sysname
DECLARE @uid int

DECLARE TGCursor CURSOR FOR
SELECT name, uid FROM sysobjects WHERE type = 'TR'
OPEN TGCursor
FETCH NEXT FROM TGCursor INTO @Trig, @uid
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQLCmd = N'DROP TRIGGER [' + user_name(@uid) + '].[' + @Trig + ']'
EXEC sp_executesql @SQLCmd
PRINT @SQLCmd

FETCH next FROM TGCursor INTO @Trig, @uid
END

CLOSE TGCursor
DEALLOCATE TGCursor

GO



chris.horne
chris.horne
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 3
This is AWESOME!!! Thanks!!!
davidfail
davidfail
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 4
I found that the given SELECT statement does not always return the proper schema name. This is far more reliable, and handles the instances where DML triggers are attached to views as well as tables:

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
ORDER BY trg.name


Stephen_W_Dodd
Stephen_W_Dodd
SSC Eights!
SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)

Group: General Forum Members
Points: 960 Visits: 553
Just a little off the main topic, but I found the enumeration script for table and views really helpful when trying to determine which objects had triggers associated with them. I modified the original to suit as follows:


SELECT ISNULL(tbl.name, vue.name) AS [schemaName]
, trg.name AS triggerName, ISNULL(tbl.tblname, vue.vuename) AS [tablename]
FROM sys.triggers trg
LEFT OUTER JOIN (SELECT tparent.object_id, ts.name, tparent.name AS tblname
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, vparent.name AS vuename
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
ORDER BY tablename, trg.name



Thanks to davidfail!!!
Stephen_W_Dodd
Stephen_W_Dodd
SSC Eights!
SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)

Group: General Forum Members
Points: 960 Visits: 553
Not to belabor the subject, but if your naming convention (poor as it is) for triggers was something like "ITRIG", "DTRIG", "UTRIG" for each table, would the script for dropping triggers need a qualifying "ON ..." clause for each table?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search