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?  
LVL 15
angus_young_acdcAsked:
Who is Participating?
 
darshan_derasariConnect With a Mentor Commented:
Hi angus_young_acdc,

You can use below syntax.

Alter table [Table name] Alter column [Column name] [New Datatype]

I assume that when you have changed data type through designer you got an error
to save the table to overcome this issue you need to make changes in the options.

Go to "Tools" >> "Options". In the dialogue box select "Designers" option left
panel. Once you select "Designer" it shows several option right side where you
will find option "Prevent saving changes that require table re-creation", please
uncheck that option and press OK.

After that you will be able change datatype through designer without permission
error.

Hope this helps.
0
 
dwkorCommented:
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

0
 
dwkorCommented:
And you also need to drop constraints/indexes (if any) which references OldCol before you drop it.
0
 
TJSimbaCommented:
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;
0
 
LowfatspreadCommented:
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....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.