Change name of 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

Change name of column

Author

Topic

duf
Starting Member

39 Posts

Posted - 2012-02-15 : 11:48:59

[code]ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name[/code]
[code]ALTER TABLE table_name RENAME COLUMN old_column_name new_column_name[/code]
None of the above query does not work. What is the correct query to change the column name?

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 11:56:58

CREATE TABLE myTable99(Col1 int)
GO

sp_rename 'myTable99.Col1', 'Col2'
GO

SELECT * FROM myTable99
GO

sp_Help myTable99
GO

DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 12:01:53

This is how Manglement Studio does it

BEGIN TRANSACTION
GO
EXECUTE sp_rename N'dbo.myTable99.Col1', N'Tmp_Col2', 'COLUMN'
GO
EXECUTE sp_rename N'dbo.myTable99.Tmp_Col2', N'Col2', 'COLUMN'
GO
COMMIT

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-15 : 15:07:05

make sure you check for dependent objects and list them all before you change column name so that you can reflect chnages to them too. Otherwise you'll end up breaking them all after the change!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-03-17 : 14:00:49

quote:
Originally posted by duf

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name

ALTER TABLE table_name RENAME COLUMN old_column_name new_column_name

None of the above query does not work. What is the correct query to change the column name?

Are you using SQL Server or ORACLE?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources