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
Chad Franklin 