Rename Column? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Rename Column?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-07-20 : 09:31:05

Hi,

Is there a way to rename column like below?

ALTER TABLE table_name RENAME COLUMN from_column TO to_column

the statement above i found it from a web site, did not worked...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-20 : 09:33:24

use sp_rename

Example from BOL

quote:
B. Rename a column
This example renames the contact title column in the customers table to title.

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

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-07-20 : 09:50:13

Hi,

I wanna do it like below but gives error Line 13: Incorrect syntax near '+'.

declare @name varchar(100)
DECLARE table_create CURSOR FOR
select Distinct (name) from sysobjects where xtype = 'U' and name != 'dtproperties'

OPEN table_create

FETCH NEXT FROM table_create into @name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_rename ''+@name+'.[pID]', 'x', 'COLUMN'

FETCH NEXT FROM table_create into @name
END

CLOSE table_create
DEALLOCATE table_create

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-20 : 09:55:34

yuk

FETCH NEXT FROM table_create into @name
WHILE @@FETCH_STATUS = 0
BEGIN
select @obj = @name + '.[pID]'
EXEC sp_rename @obj, 'x', 'COLUMN'

FETCH NEXT FROM table_create into @name
END

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-07-24 : 08:38:44

e.g.
Exec SP_Rename '<TableName.CurrentColumnName>' , '<NewColumnName>'

(KHTan, is it necessary to mention object after NewColumnName? As it mentioned in BOL. but i have tried without object name)

Mahesh

Kristen
Test

22859 Posts

Posted - 2006-07-24 : 09:12:33

"is it necessary to mention object after NewColumnName"

It avoids possible ambiguity

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources