June 7, 2006 at 7:40 am
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
June 7, 2006 at 8:09 am
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
June 7, 2006 at 8:17 am
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
June 7, 2006 at 8:24 am
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.
June 7, 2006 at 8:55 am
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
November 20, 2009 at 7:23 am
This is AWESOME!!! Thanks!!!
January 24, 2011 at 12:26 pm
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
January 25, 2011 at 9:50 am
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!!!
January 25, 2011 at 10:56 am
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?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply