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.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.