Renaming a Schema in SQL Server

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715079

    Comments posted to this topic are about the item Renaming a Schema in SQL Server

  • doug.bishop

    SSC Enthusiast

    Points: 118

    Good, clear article. I like the way you broke it down into steps, explaining each new 'part' that you added, instead of just showing the whole block of code and trying to describe what it does.

    What was not mentioned under Cleanup is the additional work that will be required.
    The stored procedures like reference the SallyDev schema and will need to be updated (e.g. SELECT ... FROM SallyDev.Class to SELECT * FROM College.Class) as will the underlying code for the view. The transfer will change the schema under which the view resides, but not the underlying code, which would still reference the SallyDev schema.

  • Jeff Moden

    SSC Guru

    Points: 994261

    doug.bishop - Tuesday, September 25, 2018 7:26 AM

    Good, clear article. I like the way you broke it down into steps, explaining each new 'part' that you added, instead of just showing the whole block of code and trying to describe what it does.

    What was not mentioned under Cleanup is the additional work that will be required.
    The stored procedures like reference the SallyDev schema and will need to be updated (e.g. SELECT ... FROM SallyDev.Class to SELECT * FROM College.Class) as will the underlying code for the view. The transfer will change the schema under which the view resides, but not the underlying code, which would still reference the SallyDev schema.

    That actually was mentioned in the "There Is No RENAME" section of the article but, to my initial thought, it wasn't emphasized enough and so people will easily miss the fact that this will break ALL code that correctly uses the 2 part naming convention.  It needs to be emphasized in big bold letters at both the beginning of the article and in the "Conclusion".

    Other than that nuance, I agree... great article.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715079

    doug.bishop - Tuesday, September 25, 2018 7:26 AM

    Good, clear article. I like the way you broke it down into steps, explaining each new 'part' that you added, instead of just showing the whole block of code and trying to describe what it does.

    What was not mentioned under Cleanup is the additional work that will be required.
    The stored procedures like reference the SallyDev schema and will need to be updated (e.g. SELECT ... FROM SallyDev.Class to SELECT * FROM College.Class) as will the underlying code for the view. The transfer will change the schema under which the view resides, but not the underlying code, which would still reference the SallyDev schema.

    That's true. I didn't cover dependent objects. I assume if you undertake this work, you know that. However, it is a simple search replace with tooling.

  • Jeff Moden

    SSC Guru

    Points: 994261

    Steve Jones - SSC Editor - Tuesday, September 25, 2018 11:29 AM

    That's true. I didn't cover dependent objects. I assume if you undertake this work, you know that. However, it is a simple search replace with tooling.

    Man, do I agree with that!  Renaming a schema is relatively easy compared to all the other work that will need to be done and you'd better know what you're doing if you do undertake such a task.  That's also a very good reason why folks need to plan schemas and go through a design review, etc, etc, before they create a new schema instead of treating it as some trivial container marking.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • ZZartin

    SSC-Dedicated

    Points: 30338

    Jeff Moden - Wednesday, September 26, 2018 7:27 AM

    Man, do I agree with that!  Renaming a schema is relatively easy compared to all the other work that will need to be done and you'd better know what you're doing if you do undertake such a task.  That's also a very good reason why folks need to plan schemas and go through a design review, etc, etc, before they create a new schema instead of treating it as some trivial container marking.

    God forbid you try to merge schemas and have object naming collisions.

Viewing 6 posts - 1 through 6 (of 6 total)

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