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.
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
EXEC sp_rename 'MySchema.OldTableName', 'NewTableName';
MySchema.OldTableName table is now named
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';
OldColumnName column is now named
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