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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
darshan_derasariCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.