how to change column name in table

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

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

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

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

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

sp_rename 'table_name'.'oldcolumn_name','newcolumn_name'

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

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

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

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

select Columnname as 'NewColumnname' from tablename

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

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

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