how to change column name in table

Posted by Gow.Net under Sql Server on 4/28/2012 | Points: 10 | Views : 53492 | Status : [Member] | Replies : 12
how to change column(field name) name in table using SQL Query

gowthaman8870226416


Responses

Posted by: A Shireesha on: 4/28/2012 [Member] Starter | Points: 25

Up
0
Down
Stu_Table



+--------+----------+-----------+
| Stu_Id | Stu_Name | Stu_Class |
+--------+----------+-----------+
| 1 | Komal | 10 |
| 2 | Ajay | 10 |
| 3 | Rakesh | 10 |
| 4 | Bhanu | 10 |
| 5 | Santosh | 10 |
+--------+----------+-----------+

Syntax

The ALTER table statement in SQL is used to modify the table 'Stu_Table' and change keyword change the name of field to new name of field. The syntax used for Alter Table is given below:



Alter table table_name change old_column_name new_column_name type size

Query

The Alter Table alter the table 'Stu_Table'. The change keyword change the column name of Stu_Id to Id in table 'Stu_Table'.



Alter table Stu_Table change Stu_Id Id varchar(10)

Stu_Table



+--------+----------+-----------+
| Id | Stu_Name | Stu_Class |
+--------+----------+-----------+
| 1 | Komal | 10 |
| 2 | Ajay | 10 |
| 3 | Rakesh | 10 |
| 4 | Bhanu | 10 |
| 5 | Santosh | 10 |
+--------+----------+-----------+


Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gow.Net on: 4/28/2012 [Member] Starter | Points: 25

Up
0
Down
it's not working i get following error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'change'.


gowthaman8870226416

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: A Shireesha on: 4/28/2012 [Member] Starter | Points: 25

Up
0
Down
SYNTAX:

sp_RENAME 'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'


ex:

CID NAME CITY
1 sai warangal
2 siri hyderabad
3 sri hanumkonda



sp_RENAME 'cust.CID' , 'ID' ,'COLUMN'


ID NAME CITY
1 sai warangal
2 siri hyderabad
3 sri hanumkonda

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gow.Net on: 4/28/2012 [Member] Starter | Points: 25

Up
0
Down
thanks
i already got it this sp method but i need using alter sql query

gowthaman8870226416

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 4/28/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

You have to use SP_RENAME.
(or)
You can use INTO statement.. But, Its risky!

[code]--Creating a Table
Create table table1
(
id int, column1 varchar(10)
)
Go
--Creationg a new table by copying the existing data with new COLUMN name
Select Id,column1 [Column2] into table2 from table1
go

--Droping an existing table name
Drop table table1
go

--Rename Table2 to Table1
[/code]But, you have to take care of Constraints and Indexes...!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nksingh420 on: 4/28/2012 [Member] Starter | Points: 25

Up
0
Down
sp_rename 'table_name'.'oldcolumn_name','newcolumn_name'

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 5/19/2012 [Member] Starter | Points: 25

Up
0
Down
To change the Column name:

EXEC sp_rename 'temp.EMPID','EMP_ID'

Regards
Sriram.R

sriram

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/18/2012 [Member] Bronze | Points: 25

Up
0
Down
We can change our column name using sp_rename

EXECUTE sp_rename 'sample2.s_quantity','qty','column'


Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muralidosscm on: 7/4/2012 [Member] Starter | Points: 25

Up
0
Down
Definition: Change is used to alter an existing column in a MySQL table. You can use change to rename a column by providing both the old column name, and the new column name. It is written as: alter table change [old column name] [new column name] varchar (30) ;
Also Known As: Change Column, Rename Column
Examples:
alter table colors change tone hue varchar (10) ;

Regards
Muralidoss M

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Oswaldlily on: 7/4/2012 [Member] Starter | Points: 25

Up
0
Down
select Columnname as 'NewColumnname' from tablename

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sharmi_Mohan on: 12/7/2012 [Member] Starter | Points: 25

Up
0
Down
Try this
ALTER TABLE table_name RENAME COLUMN old_name to new_name;

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Self-Innovator on: 12/7/2012 [Member] Bronze | Points: 25

Up
0
Down
exec sp_rename 'tablename.oldcolumnname','newcolumnname'


Join Hands Change lives
Thanks & Regards
Straight Edge Society

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response