Rename Schema

Robert Jubb 46 Reputation points
2021-03-08T14:13:09.757+00:00

Hi there,

We have had an issue where the schema name has been renamed incorrectly and now I need to rename it back again. This also applies to views, stored procedures and functions.
I intend to use the ALTER Schema Transfer to do this. My only concern is how this will affect things such as primary keys and constraints.

Has anyone any advice on this

Many thanks

Robert

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,321 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Guoxiong 8,201 Reputation points
    2021-03-08T15:48:28.107+00:00

    It should work. Here is an example to create a schema S1 and create a table under the schema S1:

    CREATE SCHEMA S1;  
    GO
    
    CREATE TABLE S1.Test   
    (
        Id int IDENTITY(1, 1) NOT NULL CONSTRAINT [PK_Database_Index] PRIMARY KEY CLUSTERED,
        TestValue varchar(10) CONSTRAINT [DF_Test_TestValue] DEFAULT ('A')
    );  
    GO 
    

    You can see the Primary Key and Default Constraint are related to S1:

    ALTER TABLE [S1].[Test] ADD  CONSTRAINT [PK_Database_Index] PRIMARY KEY CLUSTERED ([Id] ASC) ON [PRIMARY]
    GO
    
    ALTER TABLE [S1].[Test] ADD  CONSTRAINT [DF_Test_TestValue]  DEFAULT ('A') FOR [TestValue]
    GO
    

    Now create a new schema S2 and then transfer the table Test from the schema S1 to S2:

    CREATE SCHEMA S2;  
    GO
    
    ALTER SCHEMA S2 TRANSFER S1.Test;  
    GO
    

    If you script the primary key and default constraint, they are related to the schema S2 now:

    ALTER TABLE [S2].[Test] ADD  CONSTRAINT [PK_Database_Index] PRIMARY KEY CLUSTERED ([Id] ASC) ON [PRIMARY]
    GO
    
    ALTER TABLE [S2].[Test] ADD  CONSTRAINT [DF_Test_TestValue]  DEFAULT ('A') FOR [TestValue]
    GO
    
    0 comments No comments

  2. CathyJi-MSFT 21,126 Reputation points Microsoft Vendor
    2021-03-09T06:26:10.377+00:00

    Hi anonymous userJubb-3334,

    > This also applies to views, stored procedures and functions.

    Moving a stored procedure, function, view, or trigger will not change the schema name, if present, of the corresponding object either in the definition column of the sys.sql_modules catalog view or obtained using the OBJECT_DEFINITION built-in function. Therefore, we recommend that ALTER SCHEMA not be used to move these object types. Instead, drop and re-create the object in its new schema. Refer to MS document ALTER SCHEMA (Transact-SQL).

    > My only concern is how this will affect things such as primary keys and constraints.

    It also works on primary keys and constraints. Full agree with Guoxiong, please check the example that Guoxiong mentioned above.


    If the response is helpful, please click "Accept Answer" and upvote it, thank you.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  3. Robert Jubb 46 Reputation points
    2021-03-11T09:19:48.43+00:00

    Hi cathyji-msft,

    Many thanks for your reply. I have almost a 1000 objects to move to another schema, most of them, about 800 are stored procedures. Is there another way to do this instead of dropping and recreating them?

    Thanks