SQL Server清除所有触发器

最新推荐文章于 2019-05-15 14:54:00 发布

原创最新推荐文章于 2019-05-15 14:54:00 发布 · 937 阅读

1 ·

CC 4.0 BY-SA版权

Database 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一个SQL Server存储过程，用于批量删除数据库中的所有触发器。该过程通过游标遍历触发器并执行删除命令。

create PROCEDURE 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 = '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

exec sp_DropAllTriggers