Q
Problem solve Get help with specific problems with your technologies, process and projects.

Increasing the width of a column

I upsized from Access to SQL Server 7. One table had a max width of 255 (Access limits for a text field). I want...

to increase this to 500. Can you help me with the SQL to change the max width to 500, without dropping any data. I use a database manager on Brinkster, and do not have enterprise manager.

Even without Enterprise Manager, you can still do it if you have some way to submit plain SQL.

My first attempt would be --

alter table theTable
   alter column theTextfield varchar(500)

Now, not every database system is going to allow you to alter column definitions that way -- most support ALTER TABLE but only some support ALTER COLUMN. I apologize for not knowing whether SQL/Server 7 does. I suppose I could go look in the Microsoft documentation, but first I'd have to find it, then I'd have to download it, and then I'd be too tired...

So if the above approach does not work, try this --

select thePK
     , convert(varchar(500),theTextField) as theTextField 
  into copyTable
  from theTable

drop table theTable
select * into theTable from copyTable
drop table copyTable

The SELECT INTO syntax is often useful for making temporary tables for reporting, creating quick backups of tables before running mass updates, et cetera, or, as in this case, applying a modification to the table's structure.


This was last published in January 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close