How to Rename SQL Server Schema?

In SQL, we cannot RENAME a SCHEMA. To achieve this, we need to create a new SCHEMA, transfer all the contents(objects) from the old schema to new schema and then finally delete the old schema using the DROP command. The same is depicted in the below article. For this article, we will be using the Microsoft SQL Server as our database.

Step 1: Display all the current existing schemas in the system.

Query:

SELECT * FROM SYS.SCHEMAS;

Output:

Step 2: Create a schema named OLDSCHEMA.

Syntax:

CREATE SCHEMA SCHEMA_NAME;

Query:

CREATE SCHEMA OLDSCHEMA;

Output:

Step 3: Display all the current existing schemas in the system.

Query:

SELECT * FROM SYS.SCHEMAS;

Note :The OLDSCHEMA should now be visible in the list of schemas.

Output:

Step 4: Create a table TABLE1 inside the schema OLDSCHEMA. This table has 2 columns namely ID and T_NAME containing the id number and name of the entities.

Query:

CREATE TABLE OLDSCHEMA.TABLE1(
ID INT,
TNAME VARCHAR(10)
);

Output:

Step 5: Create a table TABLE2 inside the schema OLDSCHEMA. This table has 2 columns namely ID and T_NAME containing the id number and name of the entities.

Query:

CREATE TABLE OLDSCHEMA.TABLE2(
ID INT,
TNAME VARCHAR(10)
);

Output:

Step 6: Display all the tables(or objects) inside the schema OLDSCHEMA. The schema name(SCHEMA_NAME), name(TABLE_NAME), the creation date(CREATE_DATE) and the last modified date(MODIFY_DATE) of the tables are selected and displayed. An alias of the SYS.TABLES named T is used to shorten the query.

Syntax:

SELECT SCHEMA_NAME(T.SCHEMA_ID)
AS SCHEMA_NAME,
T.NAME AS TABLE_NAME, T.CREATE_DATE, 
T.MODIFY_DATE FROM SYS.TABLES T
WHERE SCHEMA_NAME(T.SCHEMA_ID)='SCHEMA_NAME' 
ORDER BY TABLE_NAME;

Query:

SELECT SCHEMA_NAME(T.SCHEMA_ID)
AS SCHEMA_NAME,
T.NAME AS TABLE_NAME, T.CREATE_DATE,
T.MODIFY_DATE FROM SYS.TABLES T
WHERE SCHEMA_NAME(T.SCHEMA_ID)='OLDSCHEMA'
ORDER BY TABLE_NAME;

Note: The two newly created tables TABLE1 and TABLE2 are now visible in the OLDSCHEMA.

Output:

Step 7: Create a schema named NEWSCHEMA.

Query:

CREATE SCHEMA NEWSCHEMA;

Output:

Step 8: Display all the current existing schemas in the system.

Query:

SELECT * FROM SYS.SCHEMAS;

Note: Both the schemas i.e. the OLDSCHEMA and the NEWSCHEMA should now be visible in the list of schemas.

Output:

Step 9: Since it is impossible to rename a schema in SQL Server, we transfer all the objects of the old schema to the newly created schema and DROP the old schema. Here in this step, we transfer the TABLE1 table from OLDSCHEMA to NEWSCHEMA.

Syntax:

ALTER SCHEMA NEW_NAMED_SCHEMA
TRANSFER OLD_NAMED_SCHEMA.TABLE_NAME;

Query:

ALTER SCHEMA NEWSCHEMA
TRANSFER OLDSCHEMA.TABLE1;

Output:

Step 10: Here in this step, we transfer the TABLE2 table from OLDSCHEMA to NEWSCHEMA.

Query:

ALTER SCHEMA NEWSCHEMA 
TRANSFER OLDSCHEMA.TABLE2;

Output:

Step 11: Display all the tables(or objects) inside the schema NEWSCHEMA. The schema name(SCHEMA_NAME), name(TABLE_NAME), the creation date(CREATE_DATE) and the last modified date(MODIFY_DATE) of the tables are selected and displayed. An alias of the SYS.TABLES named T is used to shorten the query.

Query:

SELECT SCHEMA_NAME(T.SCHEMA_ID) 
AS SCHEMA_NAME,
T.NAME AS TABLE_NAME, T.CREATE_DATE,
T.MODIFY_DATE FROM SYS.TABLES T
WHERE SCHEMA_NAME(T.SCHEMA_ID)='NEWSCHEMA' 
ORDER BY TABLE_NAME;

Note: The two tables TABLE1 and TABLE2 transferred from the OLDSCHEMA are now visible in the NEWSCHEMA.

Output:

Step 12: Now since we have successfully transferred the two tables TABLE1 and TABLE2 into the NEWSCHEMA, we no longer need the OLDSCHEMA. So, we delete the OLDSCHEMA using the DROP command.

Syntax:

DROP SCHEMA SCHEMA_NAME;

Query:

DROP SCHEMA OLDSCHEMA;

Output:

Step 13: Display all the current existing schemas in the system.

Query:

SELECT * FROM SYS.SCHEMAS;

Note: The OLDSCHEMA should not be visible now in the list of schemas, but the NEWSCHEMA must be visible.

Output: