重命名 SQL Server 中的存储过程

Rename a stored procedure in SQL Server

提问by Peter Oehlert

I'm attempting to rename a stored procedure in SQL Server 2008 with sp_rename system sproc. The third parameter is giving me difficulty though and I keep receiving the following error:

我正在尝试使用 sp_rename 系统 sproc 重命名 SQL Server 2008 中的存储过程。第三个参数虽然给我带来了困难，但我不断收到以下错误：

Msg 15249, Level 11, State 1, Procedure sp_rename, Line 75
Error: Explicit @objtype 'P' is unrecognized.

As the message indicates I'm passing in a P for the value of the parameter. I call the sproc like this:

由于消息表明我正在传递参数值的 P 。我这样称呼 sproc：

EXEC sp_rename @objName = @procName, @newname = @WrappedName, @objtype = 'P';

I double checked the documentation which says this is the value from sys.objects. I ran the following to double check I wasn't going crazy

我仔细检查了文档，其中说这是来自 sys.objects 的值。我运行以下命令仔细检查我没有发疯

select * from sys.objects where name = 'MySprocName'

and indeed the type returned is P.

实际上返回的类型是 P。

Does anyone know what I should pass here? I don't want to leave this empty since I'm creating a generic sproc to among other things rename arbitrary sprocs and if there is a name collision between a sproc and something else I don't want to have to worry about that.

有谁知道我应该在这里通过什么？我不想把它留空，因为我正在创建一个通用的 sproc 来重命名任意的 sproc，如果 sproc 和其他东西之间存在名称冲突，我不想担心。

采纳答案by JP Alioto

According to the docs, 'P' is not a correct option. You should try 'OBJECT' as that seems like the closest thing to what you're trying to do. But, you should heed this warning ...

根据文档，'P' 不是正确的选项。您应该尝试 'OBJECT'，因为这似乎是最接近您想要做的事情。但是，你应该注意这个警告......

Changing any part of an object name can break scripts and stored procedures. We recommend you do not use this statement to rename stored procedures, triggers, user-defined functions, or views; instead, drop the object and re-create it with the new name.

更改对象名称的任何部分都可能破坏脚本和存储过程。我们建议您不要使用此语句重命名存储过程、触发器、用户定义函数或视图；相反，删除对象并使用新名称重新创建它。

Also (from the same MSDNpage):

另外（来自同一个MSDN页面）：

Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name.

重命名存储过程、函数、视图或触发器不会更改 sys.sql_modules 目录视图的定义列中相应对象名称的名称。因此，我们建议不要使用 sp_rename 来重命名这些对象类型。相反，删除并使用新名称重新创建对象。

回答by Pierre-Alain Vigeant

Just omit the @objtype parameter (the default is null) and it will work.

只需省略@objtype 参数（默认为空），它就会起作用。

EXEC sp_rename 'sp_MyProc', 'sp_MyProcName'

You will receive the following warning, but the procedure will be renamed

您将收到以下警告，但该过程将被重命名

Caution: Changing any part of an object name could break scripts and stored procedures.

注意：更改对象名称的任何部分都可能破坏脚本和存储过程。

Like others stated, you should drop and recreate the procedure.

像其他人所说的那样，您应该删除并重新创建该过程。

回答by Remus Rusanu

sp_renamedoes not support procedures:

sp_rename不支持程序：

Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type.

更改当前数据库中用户创建的对象的名称。此对象可以是表、索引、列、别名数据类型或 Microsoft .NET Framework 公共语言运行时 (CLR) 用户定义类型。

Just create the new procedure with the same body and new name, then drop the old one.

只需创建具有相同主体和新名称的新程序，然后删除旧程序。

回答by Jaimal Chohan

I'm not sure about the @objtype variable, however I do know that renaming via sp_rename is bad.

我不确定 @objtype 变量，但是我知道通过 sp_rename 重命名是不好的。

When you create a stored proc, a record for it exists in sys.objects and the definition of the stored proc will be stored in sys.sql_modules.

创建存储过程时，它的记录存在于 sys.objects 中，存储过程的定义将存储在 sys.sql_modules 中。

Using sp_rename will only change the name in sys.objects, not in sys.sql_modules thus your definition will be incorrect.

使用 sp_rename 只会更改 sys.objects 中的名称，而不会更改 sys.sql_modules 中的名称，因此您的定义将不正确。

The best solution is a drop & recreate

最好的解决方案是删除并重新创建

回答by Nimish goel

sp_rename only change the name of user created objects in database. Renaming a stored procedure will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view.

sp_rename 只更改用户在数据库中创建的对象的名称。重命名存储过程不会更改 sys.sql_modules 目录视图的定义列中相应对象名称的名称。

Therefore, we recommend that you do not rename this object type. Instead, drop and re-create the stored procedure with its new name.even you should not use it use to rename to change to change the name of view, function,or trigger. If you will try to use, it will rename it, but you will also get some warning message something like this:

因此，我们建议您不要重命名此对象类型。相反，删除并使用新名称重新创建存储过程。即使您不应该使用它来重命名以更改视图、函数或触发器的名称。如果您尝试使用，它会重命名它，但您也会收到一些警告消息，如下所示：

Changing any part of an object name can break scripts and stored procedures.

更改对象名称的任何部分都可能破坏脚本和存储过程。

For more information you can visit.

欲了解更多信息，您可以访问。

http://onlyforcoder.blogspot.in/2017/11/sprename-where-to-use-where-not-to-use.htmlhttp://blog.sqlauthority.com/2008/08/26/sql-server-how-to-rename-a-column-name-or-table-name/

http://onlyforcoder.blogspot.in/2017/11/sprename-where-to-use-where-not-to-use.html http://blog.sqlauthority.com/2008/08/26/sql-server -如何重命名列名或表名/

回答by user1161391

All that Valentino Vranken said is true. However bear in mind that when you drop and create a stored procedure, you lose all of your metadata. (Create_Date, Modify_Date etc.)

Valentino Vranken 说的都是真的。但是请记住，当您删除并创建存储过程时，您将丢失所有元数据。（创建日期、修改日期等）

Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name.

重命名存储过程、函数、视图或触发器不会更改 sys.sql_modules 目录视图的定义列中相应对象名称的名称。因此，我们建议不要使用 sp_rename 来重命名这些对象类型。相反，删除并使用新名称重新创建对象。

This is also true. However I found that when you run the alter script after making the rename, it corrects the name in the definition in the modules.

这也是事实。但是我发现，当您在重命名后运行更改脚本时，它会更正模块定义中的名称。

I have wondered how the SSMS interface does all of this automagically without using T-SQL. (Perhaps it runs an alter script each time you rename an SP using the interface? I don't know.) It would be interesting is MS were to publish what is happening behind the scenes when you do a SSMS rename.

我想知道 SSMS 界面如何在不使用 T-SQL 的情况下自动完成所有这些。（也许每次您使用界面重命名 SP 时它都会运行一个更改脚本？我不知道。）有趣的是，当您执行 SSMS 重命名时，MS 会发布幕后发生的事情。

回答by Tahir77667

• The third parameter(@objtype) is required when you want to rename a database object other than a stored procedure. Otherwise it is necessary to pass a third parameter. For example renaming a column using sp_renamewill look something like this.

  USE AdventureWorks2012;  
  GO  
  EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';  
  GO
  

• To rename a user-defined stored procedure using sp_rename,you can do something like this and it literally works

• @objtype当您要重命名数据库对象而不是存储过程时，需要第三个参数 ( )。否则需要传递第三个参数。例如，使用sp_rename重命名列将如下所示。

  USE AdventureWorks2012;  
  GO  
  EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';  
  GO
  

• 要使用重命名用户定义的存储过程sp_rename，你可以做这样的事情，它确实有效

• However it is not recommendedby Microsoft, DB experts (and others here as well)to use this stored procedure as it will break things internally and might possibly incur unusual results later.Therefore it is bad. Here's what Microsoft has to say:

• 但是它是不建议通过Microsoft, DB experts (and others here as well)使用此存储过程，因为它会破坏内部的事情，有可能会招致不寻常的结果later.Therefore它是坏的。这是微软必须说的：

Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object either in the definition column of the sys.sql_modulescatalog view or obtained using the OBJECT_DEFINITIONbuilt-in function. Therefore, we recommend that sp_renamenot be used to rename these object types. Instead, drop and re-create the object with its new name.

Renaming an object such as a table or column will not automatically rename references to that object. You must modify any objects that reference the renamed object manually. For example, if you rename a table column and that column is referenced in a trigger, you must modify the trigger to reflect the new column name. Use sys.sql_expression_dependenciesto list dependencies on the object before renaming it.

重命名存储过程、函数、视图或触发器不会更改sys.sql_modules目录视图定义列中或使用OBJECT_DEFINITION内置函数获得的相应对象的名称。因此，我们建议sp_rename不要使用重命名这些对象类型。相反，删除并使用新名称重新创建对象。

重命名对象（例如表或列）不会自动重命名对该对象的引用。您必须手动修改任何引用重命名对象的对象。例如，如果重命名表列并且该列在触发器中被引用，则必须修改触发器以反映新的列名。用于sys.sql_expression_dependencies在重命名对象之前列出对象的依赖关系。

You can read about it here: sp_rename (Transact-SQL)

你可以在这里阅读它：sp_rename (Transact-SQL)

回答by MatthewMartin

In SQL 2000 days, it was safer to DROP/CREATE-- SQL used to let the meta data for the proc get out of synch when you use sp_rename.

在 SQL 2000 中，DROP/CREATE 更安全——当您使用 sp_rename 时，SQL 用于让 proc 的元数据不同步。

The best way to find out how to do fancy DDL like that is to use SSMS to rename the object while a profiler trace is attached.

找出如何执行类似 DDL 的最佳方法是在附加分析器跟踪时使用 SSMS 重命名对象。