Renaming a Schema in SQL Server

Question

Renaming a Schema in SQL Server

Steve Jones - SSC Editor

SSC Guru

Points: 734418
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

January 17, 2023 at 8:28 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 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: 1003851 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)

Steve Jones - SSC Editor SSC Guru Points: 734418 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.

Jeff Moden SSC Guru Points: 1003851 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)

ZZartin SSC-Dedicated Points: 30894 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.

fremwob Grasshopper Points: 11 More actions · Answer 6

Hi,

Really appreciated your article. I have a bunch of schemas which only needed a prefix added. I've ended up with this script which I thought you might appreciate. It creates the new schemas and transfers the content from the listed old schemas.

Without comments:

DECLARE @oldSchema VARCHAR(100), @newSchema VARCHAR(100)
DECLARE schemaCursor CURSOR FOR
SELECT name FROM sys.schemas
WHERE name IN ('OldSchema1', 'OldSchema2')
OPEN schemaCursor
FETCH NEXT FROM schemaCursor INTO @oldSchema
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @newSchema = 'ETL_' + @oldSchema
    DECLARE @cmd VARCHAR(1000)
    DECLARE objCursor CURSOR FOR
    SELECT 'ALTER SCHEMA ' + @oldSchema + ' TRANSFER ' + s.name + '.' + o.name
    FROM sys.objects o
    INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
    WHERE s.name = @oldSchema
    OPEN objCursor
    FETCH NEXT FROM objCursor INTO @cmd
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC(@cmd)
        FETCH NEXT FROM objCursor INTO @cmd
    END
    CLOSE objCursor
    DEALLOCATE objCursor
    EXEC ('CREATE SCHEMA ' + @newSchema)
    FETCH NEXT FROM schemaCursor INTO @oldSchema
END
DEALLOCATE schemaCursor

With comments:

-- Declare variables to hold the old and new schema names
DECLARE @oldSchema VARCHAR(100), @newSchema VARCHAR(100)

-- Declare a cursor to select the names of the old schemas
DECLARE schemaCursor CURSOR FOR
SELECT name FROM sys.schemas
WHERE name IN ('OldSchema1', 'OldSchema2')

-- Open the cursor and fetch the first old schema name
OPEN schemaCursor
FETCH NEXT FROM schemaCursor INTO @oldSchema

-- Start a loop to iterate through all the old schema names
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Set the new schema name by prefixing 'ETL_' to the old schema name
    SET @newSchema = 'ETL_' + @oldSchema

    -- Declare a variable to hold the command to transfer objects
    DECLARE @cmd VARCHAR(1000)

    -- Declare a cursor to select the objects in the old schema
    DECLARE objCursor CURSOR FOR
    SELECT 'ALTER SCHEMA ' + @oldSchema + ' TRANSFER ' + s.name + '.' + o.name
    FROM sys.objects o
    INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
    WHERE s.name = @oldSchema

    -- Open the cursor and fetch the first object
    OPEN objCursor
    FETCH NEXT FROM objCursor INTO @cmd

    -- Start a loop to iterate through all the objects
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Execute the command to transfer the object
        EXEC(@cmd)

        -- Fetch the next object
        FETCH NEXT FROM objCursor INTO @cmd
    END

    -- Close and deallocate the cursor for objects
    CLOSE objCursor
    DEALLOCATE objCursor

    -- Create the new schema
    EXEC ('CREATE SCHEMA ' + @newSchema)

    -- Fetch the next old schema name
    FETCH NEXT FROM schemaCursor INTO @oldSchema
END

-- Close and deallocate the cursor for schemas
DEALLOCATE schemaCursor

This reply was modified 1 year, 3 months ago by fremwob. Reason: un-commented code might be easier to read

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 7

Thanks, nice to have scripts like this. If you're like to submit as a script in the library, please feel free: https://www.sqlservercentral.com/contributions

Or, if you're like to write an article showing how you've used this and give a scenario and then show how the code runs and works (and any limitations), love to see that as well.