SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_rename and the procedure definition


sp_rename and the procedure definition

Author
Message
Lowell
Lowell
SSC Guru
SSC Guru (184K reputation)SSC Guru (184K reputation)SSC Guru (184K reputation)SSC Guru (184K reputation)SSC Guru (184K reputation)SSC Guru (184K reputation)SSC Guru (184K reputation)SSC Guru (184K reputation)

Group: General Forum Members
Points: 184815 Visits: 41569
Comments posted to this topic are about the item sp_rename and the procedure definition

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Hany Helmy
Hany Helmy
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7291 Visits: 1122
Good question, didn`t touch sp_rename since ages :-)
Thx.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)

Group: General Forum Members
Points: 147002 Visits: 13350
Great question, and a fine example of why sp_rename should be avoided.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Carlo Romagnano
Carlo Romagnano
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12418 Visits: 3517
sp_rename leave unaltered the definition, (see sys.syscomments)
create procedure My_proc
as
select 1 as a
go
SELECT * FROM sys.syscomments
WHERE object_name(id) = 'My_proc'
go
exec sp_rename My_proc,Myproc
SELECT * FROM sys.syscomments
WHERE object_name(id) = 'Myproc'


After the rename the definition in the columnn "text" is unaltered.
The only advantage of sp_rename instead of "drop and re-create" is that you shouldn't reassign permissions on procedure.
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35638 Visits: 7703
sp_rename is usefull for tables and columns, but avoid like the plague for the rest.

nice, question, thanks Lowell

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
matthew.flower
matthew.flower
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1183 Visits: 364
It says it very clearly here: http://msdn.microsoft.com/en-gb/library/ms188351.aspx

"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."

"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.
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_dependencies to list dependencies on the object before renaming it."
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)

Group: General Forum Members
Points: 158520 Visits: 11666
Nice question. Thanks.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
SqlMel
SqlMel
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1547 Visits: 482
It's a good question. Learned something today.

Actually, if you run the script as is it will not even rename the object.

Since it is lacking a GO statement between the SELECT and the EXECUTE, the last statement just becomes part of the procedure.

At first, I wondered if that intentionally part of the actual question Smile

---------------
Mel. Cool
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)

Group: General Forum Members
Points: 158520 Visits: 11666
SqlMel (8/13/2014)
It's a good question. Learned something today.

Actually, if you run the script as is it will not even rename the object.

Since it is lacking a GO statement between the SELECT and the EXECUTE, the last statement just becomes part of the procedure.

At first, I wondered if that intentionally part of the actual question Smile

Yeah, me too. But then I re-read the question and it was pretty clear.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Michael_Garrison
Michael_Garrison
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2240 Visits: 2754
Yep, good question, learned something today and I answered the question AFTER getting my coffee.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search