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
at

2 comments:

  1. KansasCoder10:00 AM

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

    ReplyDelete
  2. Denis10:52 AM

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

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


    so basically

    ReplyDelete