asked on

Script to Change Data Type of Column

Hello,

I have to change the data type of a column in my DB from an Int to a VarChar(500), I can't do this via the designer as I will have to provide a script to our SQL Admins team, but I have no idea how to do this. I'm had a look at a couple of methods, but most seem to involve dropping the table - which currently has data - and creating it again. I've had a look at using the Generate Change Script button, but due to permissions I can't use it.

How can I create a script to change the datatype to varchar(500) from int, and cast all current data in that column to be a varchar?

dwkor

Something like that.

Drop column does not rebuild the rows. As result, you will have ghost 4 bytes per row unless you rebuild the clustered index. If you table has a lot of records, it would be time consuming operation.

alter table dbo.T
add
	NewCol varchar(500)
go

update dbo.T
set
	NewCol = CONVERT(varchar(500), OldCol)
go

alter table dbo.T
drop column OldCol
go

exec sp_rename 'dbo.T.NewCol','OldCol','COLUMN'
go

alter index PK_CLUSTERED_INDEX on dbo.T REBUILD
go

Open in new window

dwkor

And you also need to drop constraints/indexes (if any) which references OldCol before you drop it.

TJSimba

CREATE TABLE MyTable (columnA int);
INSERT INTO MyTable VALUES (123);
INSERT INTO MyTable VALUES (123);
INSERT INTO MyTable VALUES (123);
INSERT INTO MyTable VALUES (123);
INSERT INTO MyTable VALUES (123);
INSERT INTO MyTable VALUES (123);
INSERT INTO MyTable VALUES (123);
ALTER TABLE MyTable ALTER COLUMN columnA varchar(30);
SELECT * FROM MyTable;

Lowfatspread

with a design change of that extreme

whilst an
alter table tablename alter column columnname varchar(500)

command would make the change...

there are other aspects to underlying database design/table access that should be addressed...

you'd be best off handing the requirments to your dba.. to process

e.g.

underlying table organisation, expected new table size, table growth, indexing issues?

plus ensuring database backup and restore points are clearly understood, as well as complete testing of all application and batch processes....

ASKER CERTIFIED SOLUTION

darshan_derasari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial