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 (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73315 Visits: 40966
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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

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

Group: General Forum Members
Points: 63580 Visits: 13298
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
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7568 Visits: 3399
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.

I run on tuttopodismo
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17311 Visits: 7421
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.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1080 Visits: 359
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 (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50169 Visits: 10844
Nice question. Thanks.


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

Group: General Forum Members
Points: 1057 Visits: 481
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 (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50169 Visits: 10844
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1604 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