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 system 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和其他东西之间有名称冲突，我就不用担心了。
7 个解决方案
#1
17
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 MSDN page):
同样(来自同一个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来重命名这些对象类型。相反，删除并重新创建具有新名称的对象。
#2
38
Just omit the @objtype parameter (the default is null) and it will work.
只要省略@objtype参数(默认值为null)就可以了。
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.
正如其他人所说，您应该删除并重新创建过程。
#3
2
sp_rename does 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.
只需创建具有相同主体和新名称的新过程，然后删除旧过程。
#4
1
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.
当您创建一个存储的proc时，它在sys中是有记录的。对象和存储的proc的定义将存储在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中更改名称。对象,而不是在系统。因此，您的定义将是错误的。
The best solution is a drop & recreate
最好的解决方法是“滴-重”
#5
0
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天中，删除/创建更安全——使用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重命名对象。
#6
0
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.)
瓦伦蒂诺说的都是真的。但是请记住，当您删除并创建存储过程时，您将丢失所有的元数据。(Create_Date Modify_Date等等)。
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.
这也是真实的。但是我发现，当您在重命名之后运行alter脚本时，它会纠正模块中定义中的名称。
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时，它都会运行alter脚本?)我不知道。)有趣的是，当你重命名SSMS时，MS会发布幕后发生的事情。
#7
0
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.html http://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-how-to-rename-a-column-name-or-table-name/
#1
17
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 MSDN page):
同样(来自同一个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来重命名这些对象类型。相反，删除并重新创建具有新名称的对象。
#2
38
Just omit the @objtype parameter (the default is null) and it will work.
只要省略@objtype参数(默认值为null)就可以了。
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.
正如其他人所说，您应该删除并重新创建过程。
#3
2
sp_rename does 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.
只需创建具有相同主体和新名称的新过程，然后删除旧过程。
#4
1
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.
当您创建一个存储的proc时，它在sys中是有记录的。对象和存储的proc的定义将存储在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中更改名称。对象,而不是在系统。因此，您的定义将是错误的。
The best solution is a drop & recreate
最好的解决方法是“滴-重”
#5
0
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天中，删除/创建更安全——使用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重命名对象。
#6
0
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.)
瓦伦蒂诺说的都是真的。但是请记住，当您删除并创建存储过程时，您将丢失所有的元数据。(Create_Date Modify_Date等等)。
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.
这也是真实的。但是我发现，当您在重命名之后运行alter脚本时，它会纠正模块中定义中的名称。
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时，它都会运行alter脚本?)我不知道。)有趣的是，当你重命名SSMS时，MS会发布幕后发生的事情。
#7
0
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.html http://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-how-to-rename-a-column-name-or-table-name/