Tuesday, April 22, 2008

How to rename a column in a SQL Server table without using the designer

If you have a table and you want to rename a column without using the designer, how can you do that?

First create this table

CREATE TABLE TestColumnChange(id int)
INSERT TestColumnChange VALUES(1)

SELECT * FROM TestColumnChange

As you can see the select statement returns id as the column name. you can use ALTER table ALTER Column to change the dataype of a column but not the name.

Here is what we will do, execute the statement below

EXEC sp_rename 'TestColumnChange.[id]', 'value', 'COLUMN'

Now do the select, you will see that the column name has changed

SELECT * FROM TestColumnChange

That is it, very simple


Prairie Runner said...

can you reports without using generic data in your explanation..it makes your solution ambiguous. can you repost using sample data?

SQL said...

This example renames the contact title column in the customers table to title.

EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'

so basically