SQL Change column name and type

I want change existing table column name and data type

The faster way is drop and create
If table already have data inside and we don't wanna recreate table, we can do this :

Original table structure
create table HumanResources.Gender2
(id char(1),
lang varchar(20),
Name varchar(20))

Execute below query to change column name
sp_RENAME 'HumanResources.Gender2','lang', 'lang_id' , 'COLUMN'
Caution: Changing any part of an object name could break scripts and stored procedures.

Identify store procedure which reference to Gender2.lang table

Example :
create procedure GetGender2
@id int
as
select lang,name from Gender2 where
id =@id

To identify table, trigger, view reference to Gender2.lang table, execute this
sp_depends 'Gender2'-- Gender2 is the table name

In the current database, the specified object is referenced by the following:
name type
dbo.GetGender2 stored procedure

After change the column name and excute the above store procedure, SQL will give this error message:
Msg 207, Level 16, State 1, Procedure GetGender2, Line 4
Invalid column name 'lang'.


We should change all store procedure which reference to this table

In this case, we need change Store Procedure GetGender2, change lang to lang_id

alter procedure GetGender2
@id int
as
select lang_id,name from Gender2 where
id =@id

I execute the second query to change the data type
alter table HumanResources.Gender2
ALTER COLUMN lang_id int
Data type change successful

If original table already have data and data as below:
insert into Gender2
values ('English','This is Male')
insert into Gender2
values ('English','This is Female')

When we run
alter table HumanResources.Gender2
ALTER COLUMN lang_id int
SQL will give error :
- Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'English' to data type int.
The statement has been terminated.

We fail to change the data type because original data is character, we can't change the data type to integer.


Reference :
SQL SERVER – How to Rename a Column Name or
Table Name

Listing SQL Server Object Dependencies