SQL Server清除所有触发器

blizzardlyk

于 2015-12-18 13:59:51 发布

阅读量917

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Database

本文链接：https://blog.youkuaiyun.com/blizzardlyk/article/details/50351377

Database 专栏收录该内容

1 篇文章

订阅专栏

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

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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