Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

ALTER DATABASE - MODIFY COLUMN

0 views
Skip to first unread message

Karell Ste-Marie

unread,
Jan 19, 1998, 3:00:00 AM1/19/98
to

I tried to find this one on the net but can't seem to find the right FAQ
page anywhere.

How to you modify a column's width ?

I have a VARCHAR column which is at 12 characters and I want it up to 14
characters.

I tried this, but it was not accepted by MS-SQL 6.5:

ALTER TABLE [TABLE NAME] MODIFY ([COLUMN NAME], [COLUMN TYPE]([NEW WIDTH]))

So if my table name's Authors it would look like this:
ALTER TABLE AUTHORS MODIFY (email varchar(14))

What's the right way to do it ?

Thanks,

Karell Ste-Marie
MIS Manager
CTI Datacom Inc.
2250 Place Transcanadienne
Dorval, Quebec
H9P 2X5
Phone: (514) 683 6363 .28
Fax: (514) 683 7997
Toll: (888) CTI 4 ATM
karell.s...@ctidata.com
www.ctidata.com


Anthony V. Goldberg

unread,
Jan 19, 1998, 3:00:00 AM1/19/98
to

On 19 Jan 1998 18:03:58 GMT, "Karell Ste-Marie"
<karell.s...@ctidata.com> wrote:
There is no way to alter a column width in MS SQL. Workaround is
simple - rename old table (sp_rename) and create new one with desired
width of column and upload data from old to new table (INSERT INTO).

>I tried to find this one on the net but can't seem to find the right FAQ
>page anywhere.
>
>How to you modify a column's width ?
>

>Karell Ste-Marie

Anton V. Goldberg,
MCSE...
My opinions are only MY.

Niener

unread,
Feb 4, 1998, 3:00:00 AM2/4/98
to

Altering fields: I am lazy. I'll open the Enterprise Manager, select the
table, Choose 'Objects', 'Generate SQL Script' and generate a script for
re-creating the old table. Then I edit the script so that the table
being created has a new name. I change the field size of the offending
field (and sometimes drop fields from the definition, best way I know of
to get rid of an old, unused field)

I run the script to create the new table. I then write a script, as
mentioned, to insert into the new table, or alternatively, if small
dataset, connect with Microsoft Access and use an append query to append
the data from the old table to the new table. This has the added
advantage of doing a lot of the type conversions for me, or making them
easier to see and write.

Then when all the data is in my new table, I drop the original table,
and rename the new one to the old one's name....

0 new messages