删除数百个表触发器和数百个存储过程的最快方法？ [英] Fastest way to delete hundreds of table triggers and hundreds of stored procedures?

问题描述

如何快速删除所有用户存储过程和所有表格触发器

单个数据库？


谢谢你是

解决方案

你可以在查询分析器中运行以下脚本。确定你在

正确的数据库。


USE MyDatabase

DECLARE @DropStatement nvarchar（4000）

DECLARE DropStatements CURSOR

LOCAL FAST_FORWARD READ_ONLY FOR

SELECT N''DROP''+

CASE xtype

当''P''那么'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' >
QUOTENAME（USER_NAME（uid））+

N''。''+

QUOTENAME（姓名）

来自sysobjects

WHERE xtype IN（''P''，''TR''）

OPEN DropStatements

WHILE 1 = 1

BEGIN

FETCH NEXT FROM DropStatements INTO @DropStatement

IF @@ FETCH_STATUS = -1 BREAK

EXEC（@DropStatement）

结束

关闭DropStatements

DEALLOCATE DropStatements


-

希望这会有所帮助。


Dan Guzman

SQL Server MVP


" serge" < SE **** @ nospam.ehmail.com>在消息中写道

新闻：VR ********************* @ news20.bellglobal.com ...

如何在单个数据库中快速删除所有用户存储过程和所有表格触发器？

谢谢

我今天会试试这个。


谢谢

你可以在查询分析器中运行以下脚本。确保你在正确的数据库中。

使用MyDatabase
DECLARE @DropStatement nvarchar（4000）
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR <选择N'DROP''+
CASE xtype
当'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '
结束+
QUOTENAME（USER_NAME（uid））+
N''。''+
QUOTENAME（姓名）
FROM sysobjects
WHERE xtype IN（''P''，''TR''）
OPEN DropStatements
WHILE 1 = 1
BEGIN
下一个来自DropStatements INTO @DropStatement
IF @ @FETCH_STATUS = -1 BREAK
EXEC（@DropStatement）
END
CLOSE DropStatements
DEALLOCATE DropStatements

" serge" < SE **** @ nospam.ehmail.com>在消息中写道

新闻：oE ********************* @ news20.bellglobal.com ...

今天我会试试。



因为我们发现美国银行因为他们的数据库不知何故而离线

在黑客攻击中丢失了他们的整个架构。 :-)

谢谢

您可以在查询分析器中运行以下脚本。确保你在正确的数据库中是
。

使用MyDatabase
DECLARE @DropStatement nvarchar（4000）
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR <选择N'DROP''+
CASE xtype
当'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '
结束+
QUOTENAME（USER_NAME（uid））+
N''。''+
QUOTENAME（姓名）
FROM sysobjects
WHERE xtype IN（''P''，''TR''）
OPEN DropStatements
WHILE 1 = 1
BEGIN
下一个来自DropStatements INTO @DropStatement
IF @ @FETCH_STATUS = -1 BREAK
EXEC（@DropStatement）
END
CLOSE DropStatements
DEALLOCATE DropStatements

How can i delete all user stored procedures and all table triggers very fast
in
a single database?

Thank you

解决方案

You can run the following script in Query Analyzer. Be certain you are in
the correct database.

USE MyDatabase
DECLARE @DropStatement nvarchar(4000)
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT N''DROP '' +
CASE xtype
WHEN ''P'' THEN N''PROCEDURE ''
WHEN ''TR'' THEN N''TRIGGER ''
END +
QUOTENAME(USER_NAME(uid)) +
N''.'' +
QUOTENAME(name)
FROM sysobjects
WHERE xtype IN(''P'', ''TR'')
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @DropStatement
IF @@FETCH_STATUS = -1 BREAK
EXEC(@DropStatement)
END
CLOSE DropStatements
DEALLOCATE DropStatements

--
Hope this helps.

Dan Guzman
SQL Server MVP

"serge" <se****@nospam.ehmail.com> wrote in message
news:VR*********************@news20.bellglobal.com ...

How can i delete all user stored procedures and all table triggers very
fast
in
a single database?

Thank you

I will try this today.

Thank you

You can run the following script in Query Analyzer. Be certain you are in
the correct database.

USE MyDatabase
DECLARE @DropStatement nvarchar(4000)
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT N''DROP '' +
CASE xtype
WHEN ''P'' THEN N''PROCEDURE ''
WHEN ''TR'' THEN N''TRIGGER ''
END +
QUOTENAME(USER_NAME(uid)) +
N''.'' +
QUOTENAME(name)
FROM sysobjects
WHERE xtype IN(''P'', ''TR'')
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @DropStatement
IF @@FETCH_STATUS = -1 BREAK
EXEC(@DropStatement)
END
CLOSE DropStatements
DEALLOCATE DropStatements

"serge" <se****@nospam.ehmail.com> wrote in message
news:oE*********************@news20.bellglobal.com ...

I will try this today.

As we discover Bank of America goes offline since their databases somehow
lost their entire schema in a hacker attack. :-)

Thank you

You can run the following script in Query Analyzer. Be certain you are in the correct database.

USE MyDatabase
DECLARE @DropStatement nvarchar(4000)
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT N''DROP '' +
CASE xtype
WHEN ''P'' THEN N''PROCEDURE ''
WHEN ''TR'' THEN N''TRIGGER ''
END +
QUOTENAME(USER_NAME(uid)) +
N''.'' +
QUOTENAME(name)
FROM sysobjects
WHERE xtype IN(''P'', ''TR'')
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @DropStatement
IF @@FETCH_STATUS = -1 BREAK
EXEC(@DropStatement)
END
CLOSE DropStatements
DEALLOCATE DropStatements

这篇关于删除数百个表触发器和数百个存储过程的最快方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！