sp_rename and the procedure definition

  • Lowell

    SSC Guru

    Points: 323359

    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

    SSChampion

    Points: 13321

    Good question, didn`t touch sp_rename since ages 🙂

    Thx.

  • Koen Verbeeck

    SSC Guru

    Points: 258909

    Great question, and a fine example of why sp_rename should be avoided.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Carlo Romagnano

    SSC-Insane

    Points: 21712

    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.

  • This was removed by the editor as SPAM

  • matthew.flower

    Ten Centuries

    Points: 1381

    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

    SSC Guru

    Points: 286957

  • SqlMel

    SSCrazy

    Points: 2891

    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 🙂

    ---------------
    Mel. 😎

  • Ed Wagner

    SSC Guru

    Points: 286957

    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 🙂

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

  • Michael_Garrison

    Hall of Fame

    Points: 3050

    Yep, good question, learned something today and I answered the question AFTER getting my coffee.

  • Thomas Abraham

    SSChampion

    Points: 10761

    Excellent question. Loved the text explaining the things you weren't trying to trick us with. 😉

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • twin.devil

    SSC-Insane

    Points: 22208

    Nice question. Thanks for sharing

  • stephen.long.1

    SSCrazy

    Points: 2568

    Nice question. I've never used sp_rename, and now I know that I shouldn't use it. 🙂

    I also noticed the missing go statement, but when the available options didn't mention it, I figured it was unintentional.

  • TomThomson

    SSC Guru

    Points: 104763

    Interesting question. It's a pity that the first answer option is ambiguous, with one reading making it correct and the other incorrect, while the second option, which is claimed to be the correct answer, is just plain incorrect.

    The definition is changed to use the new name in the text defining the proc's name; that's one interpretation of the first option, and is the only correct answer.

    The definition is changed to use the new name everywhere it had the old name; that's another interpretation of the first option, and is an incorrect answer.

    "The definition is untouched" - anything that says that is wrong, because the definition of a stored procedure includes its name, which is changed. It's in the first bit of the text returned by SELECT object_definition(Object_id(<name of sp>)) when that returns any text, rather than NULL. The explanation mentions that stored procedure, so it's a bit surprising that the question's author apparently didn't notice it. So the second option is incorrect. Of course the second option also states that the procedure now contains the new name, which is true because the body contains that name, but since the second option also makes the incorrect claim that the definition is untouched the second option is not a correct answer.

    Some of the earlier comments suggest that renaming SPs (and views and so on) is more of a problem that renaming other things like tables. I don't agree. Renaming most things is fraught: that's because everything that uses the name has to be altered. Tables are no easier than stored procedures: every script or stored procedure that uses the table has to be changed to use the new name. So does every view and every function that uses the table, and since views and functions can't call stored procedures that makes a table more awkward to rename than a stored procedure. This always makes me think that people who claim rename is less of a problem with tables than with SPs are not actually correct. I guess it depends on the extent to which sys.object_definition() and the sys.sql_modules view are important; to me they don't seem to be very important at all - SSMS gets the SP definitions correct when asked to script them and the sql system invokes the correct SP when asked to despite object_definition(), and hence sys.sql_modules, delivering NULL for the definitions of renamed SPs (and SSMS gets it right views and so on, too). When I want an SP or View definition, I ask SSMS for a script. When I want to call an SP or use a view in a DML statement object_definition() isn't involved. So why should I care that object_definition() can't provide the definition of a renamed view or trigger or SP or whatever? Oddly enough I do care, because maybe object_definition() will in future be needed for something I want to handle; so I shouldn't use sp_rename where it breaks object_definition(); but equally I can see that because object_definition doesn't work for some renamed things, in the future maybe it won't work for others - so I shouldn't use sp_rename for tables either. In practice the only things I have ever used sp_rename for in production code are databases (but I use it for prettty well anything when not in production).

    Tom

  • Dana Medley

    SSCertifiable

    Points: 6764

    stephen.long.1 (8/13/2014)


    Nice question. I've never used sp_rename, and now I know that I shouldn't use it. 🙂

    +1 Thanks so much for the question. I learned something new today.



    Everything is awesome!

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply