Hi all,

I have to do small thing is SQL server. rename the existing column name and update the character of that column but all the values of column in it. for eg

Number Code Amount
1001 W 123.45
1001 A 123.45
1002 B 78.90
1003 C 86753.09

I need to change Number into ID and keep all the values in it. also right now number has character int(10) which needs to change into int(6). I know i can do this by right clicking that column and clicking design table and changing everything out there but the thing is i need this in script. Can somebody help me to do this in simplest way.

exec SP_Rename 'tablename.oldcolumnname','newcolumname','Column'

this proc works fine but can we write our own script

try this one to change the data type

ALTER TABLE
MODIFY (Number int(6) NOT NULL );



Try this one to rename the column Name

EXEC sp_rename
@objname = 'table_name.old_column_name',
@newname = 'new_column_name',
@objtype = 'COLUMN'

View/Share this post only

correction on first one

ALTER TABLE tableName
MODIFY (Number int(6) NOT NULL );