Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Drop all triggers in a given database Expand / Collapse
Author
Message
Posted Wednesday, June 7, 2006 7:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, July 2, 2011 9:53 PM
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



Post #285605
Posted Wednesday, June 7, 2006 8:09 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 870, Visits: 897
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
Post #285616
Posted Wednesday, June 7, 2006 8:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, July 2, 2011 9:53 PM
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



Post #285621
Posted Wednesday, June 7, 2006 8:24 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 870, Visits: 897
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
Post #285629
Posted Wednesday, June 7, 2006 8:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, July 2, 2011 9:53 PM
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



Post #285644
Posted Friday, November 20, 2009 7:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, August 28, 2010 1:20 PM
Points: 1, Visits: 3
This is AWESOME!!! Thanks!!!
Post #822384
Posted Monday, January 24, 2011 12:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 25, 2011 11:09 AM
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

Post #1052628
Posted Tuesday, January 25, 2011 9:50 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 18, 2013 6:12 AM
Points: 952, Visits: 552
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!!!
Post #1053307
Posted Tuesday, January 25, 2011 10:56 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 18, 2013 6:12 AM
Points: 952, Visits: 552
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?
Post #1053350
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse