Renaming a Schema in SQL Server

Question

Renaming a Schema in SQL Server

Steve Jones - SSC Editor

SSC Guru

Points: 728164
More actions
September 25, 2018 at 12:02 am

#415728

Comments posted to this topic are about the item Renaming a Schema in SQL Server
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

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

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

doug.bishop SSC Enthusiast Points: 118 More actions · Answer 1

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: 1000539 More actions · Answer 2

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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 3

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.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

ZZartin SSC-Dedicated Points: 30758 More actions · Answer 5

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.