Development, T-SQL

How to Rename a Table Name or a Column Name

So, you want rename a table, or a column name? It’s a simple command, but it’s one of those commands I don’t use often and therefore forget how to write it every time I need to use it. Maybe you’re like me and can’t remember how it should be written too. Keep reading…

To do this, the sp_rename stored procedure will achieve our goal.

Rename Table

The following code shows shows the syntax to change the name of a table:

EXEC sp_rename '<Table Schema Name>.<Old Table Name>', '<New Table Name>';

Lets use an example.

I’ll create a table called MySchema.OldTableName and insert a record to it.

Now I’ll rename the table to MySchema.NewTableName

EXEC sp_rename 'MySchema.OldTableName', 'NewTableName';

The MySchema.OldTableName table is now named MySchema.NewTableName.

You can see the new table name in the query and it returns the same value with the same old column name.

Rename Column Name

Now lets rename the column. The following code shows shows the syntax to change the name of a column. Note the difference to renaming a table, the 3rd argument stating COLUMN is required:

EXEC sp_rename '<Table Schema Name>.<Table Name>.<Old Column Name>', '<New Column Name>', 'COLUMN';

Using the same example as before, I’ll now rename the column:

EXEC sp_rename 'MySchema.NewTableName.OldColumnName', 'NewColumnName', 'COLUMN';

The OldColumnName column is now named NewColumnName.

You can see the new column name in the query and it returns the same value.

Rename Schema Name

You may be wondering what if you wanted to rename a schema name? Well, you can’t. Not at the time of me writing this anyway (May 2021). To do it, you’ll need to create a new schema and then transfer ownership of the tables to that schema instead.

I’ll use the same example to show this too:

CREATE SCHEMA NewSchema;
GO

ALTER SCHEMA NewSchema TRANSFER MySchema.NewTableName;  
GO  

The old schema called MySchema, well, that is still called MySchema. But now my table has been transferred to the new schema called NewSchema

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s