How can i rename my column in sql table?

Question

how can i rename column name via alter table in Ms sql 2005 forexample:

alter table tablename rename "old col name" to "new col name"

Answer 1

sp_rename as described here.

Though I seem to remember that it can't always be used.

Answer 2

sp_rename 'TableName.ColumnName', 'NewColumnName', 'COLUMN'

Answer 3

i want to make it without sp_rename how can i do that alter..

You can't. You can create a new column in the table, using the new name, copy the contents of the old column into the new column, and then drop the old column (that's two ALTERs and an UPDATE), but the only way to do it otherwise is sp_rename.

Here's a link to the ALTER TABLE documentation, where you can see what options are available to you. Change isn't one of them.

This section of the documentation covers what you can do as part of an ALTER COLUMN clause of ALTER TABLE:

ALTER COLUMN column_name 
{ 
    [ type_schema_name. ] type_name [ ( { precision [ , scale ] 
        | max | xml_schema_collection } ) ] 
    [ COLLATE collation_name ] 
    [ NULL | NOT NULL ] 
| {ADD | DROP } { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION}
}

Note, there's no mention of a new name. So, again, to repeat, you can't rename a column using ALTER TABLE, in SQL Server. If they implemented the standard syntax (which they don't), it would be ALTER TABLE [table_name] RENAME {COLUMN} [column_name] TO [new_column_name]