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

Anyone knows how to alter a column's datatype and length?

0 views
Skip to first unread message

Kuo C Yeh

unread,
Mar 12, 1997, 3:00:00 AM3/12/97
to

Does anyone know how to alter a column's datatype and length in SQL Server
6.5? Since SQL Server provides only 'Add column' in ALTER command, and
Enterprise Manager 'Edit Table' can only change column names, how do I
change a column's datatype and length?


Ben McEwan

unread,
Mar 12, 1997, 3:00:00 AM3/12/97
to

In article <01bc2e9d$7d29b600$0b9127ca@isdt>, From "Kuo C Yeh"
<is...@isdt.taiping.com.tw>, the following was written:

You can't. You must drop and recreate the table. There is no "alter
table drop column", as you've noticed.

In EM, the name change is really just an "sp_rename 'table.col',
'newcol'"

--
03/12/97 16:21
Ben McEwan
Geist, LLC - http://www.metis.com/~geist
bmc...@metis.com.


Neil Pike

unread,
Mar 13, 1997, 3:00:00 AM3/13/97
to

>
> Does anyone know how to alter a column's datatype and length in SQL Server
> 6.5? Since SQL Server provides only 'Add column' in ALTER command, and
> Enterprise Manager 'Edit Table' can only change column names, how do I
> change a column's datatype and length?
>
Kuo,

You can't - you'll have to create a new table.

Neil Pike MVP/MCSE (neil...@natwestuk.co.uk)
Protech Computing Ltd (MS Solution Provider)
Using Virtual Access 3.52 build 159c (32-bit) on NT 4.0 SP2

Brian Elliott

unread,
Mar 13, 1997, 3:00:00 AM3/13/97
to

Ahhh... the ol' "How do you change the data type/ length" question.

Well you can't.

What you can do is create a new table (with all the columns/data
types/lengths that you want).

Copy the data from the old table in to the new table (using a INSERT/SELECT
FROM - check the books online).

Delete the old table

Rename the new table the old table's name.

Recreate any objects the reference the old table (views, etc).

--- Brian

Kuo C Yeh <is...@isdt.taiping.com.tw> wrote in article
<01bc2e9d$7d29b600$0b9127ca@isdt>...

Bill Talada

unread,
Mar 14, 1997, 3:00:00 AM3/14/97
to

Kuo C Yeh wrote:
>
> Does anyone know how to alter a column's datatype and length in SQL Server
> 6.5? Since SQL Server provides only 'Add column' in ALTER command, and
> Enterprise Manager 'Edit Table' can only change column names, how do I
> change a column's datatype and length?

1. sp_rename 'mytab.mycol', xmycol, column

2. alter table mytab add mycol newdatatype

3. update table mytab set mycol = convert(varchar(30),oldcol)
might need to use a cursor here for low impact during production.

4. Drop xmycol when you have time.

Another alternative is to throw a view in front of the table.
Or add a constraint to shorten a varchar column.

Chris Oakleaf

unread,
Mar 14, 1997, 3:00:00 AM3/14/97
to

And after you rename the new table to the old table name......

DON'T FORGET to set the permissions. :)

0 new messages