Rename Schema

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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