How To Change Schema Of A Table In SQL Server

My team recently received a requirement to change the schema of a table in the SQL server database. We have identified a few quick approaches to this task. In this article, I will walk you through each approach individually.

Approach-1 Using ALTER SCHEMA Statement

The primary approach is to use the ALTER SCHEMA Statement to achieve this.

Syntax

ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.TableName;

Example

The below query is to transfer the schema of the Vehicle table from dbo to USA.

ALTER SCHEMA USA TRANSFER dbo.Vehicle;

After executing the above query, I got the expected output below, which changed the schema from dbo to USA.

To check if it is actually done, I executed the query below.

SELECT * FROM INFORMATION_SCHEMA.TABLES;

You can see the schema of the Vehicle table changed successfully, as shown in the screenshot below.

Check out How To Create A Schema In SQL Server

Approach-2: Using a Custom Stored Procedure

You can write a custom stored procedure using the below query.

CREATE PROCEDURE TransferTableSchema
    @OldSchemaName NVARCHAR(130),
    @NewSchemaName NVARCHAR(130),
    @MyTableName NVARCHAR(130)
AS
BEGIN
    DECLARE @MyQuery NVARCHAR(MAX);
    SET @MyQuery = N'ALTER SCHEMA ' + QUOTENAME(@NewSchemaName) + 
               N' TRANSFER ' + QUOTENAME(@OldSchemaName) + N'.' + QUOTENAME(@MyTableName);
    EXEC sp_executesql @MyQuery;
END

The above query was executed successfully, as shown in the screenshot below.

Now, we can use the below query to execute the stored procedure, which will change the schema name from USA to UK.

EXEC TransferTableSchema 'USA', 'UK', 'Vehicle';

Now, see the screenshot below. The query was executed successfully, and the schema for the Vehicle table changed from USA to UK.

Check out How To Copy Table Schema In SQL Server

Approach-3 Create a brand new table with the new schema

Here, we can create a new table with the new schema and then transfer the data from the old table to the new one.

1. Use the below query to create a new table with the new schema. The query will create a new table named NewProduct with the new schema USA.

CREATE TABLE USA.NewProduct (
    ProductName Nvarchar(50),
	ProductType Nvarchar(50),
	ProductID int,
	ProductBrand Nvarchar(50)

);

2. Now, we can insert data from the old table into the new table using the query below.

INSERT INTO USA.NewProduct
SELECT * FROM dbo.Product;

3. Now, you can use the below query to drop the table with the old schema.

DROP TABLE dbo.Product;

4. Finally, we can execute the below query to rename the old table.

EXEC sp_rename 'USA.NewProduct', 'Product';

Conclusion

Changing the schema of a table in SQL Server is simple. As mentioned in this article, you can achieve this using the ALTER SCHEMA Statement, a stored procedure, or by creating a new table with the new schema. Choose the approach that best meets your requirements.

You may also like following the articles below.

• How to Get Table Row Count in SQL Server
• How To Find A Table In SQL Server
• How To Delete All Records From A Table In SQL Server