changing column name

[doddo]

Hi could anybody tell me how to change the type of a column?

the following doesn't work:

ALTER table users rename column ch_1_year to ch_1_year int

I want to change to Integer from Varchar

Thanks
Rich

 

[wushutwist]

You can't change column types like that.
Process:
1) Copy contents of column to temporary table
2) Drop column
3) Recreate column as Integer type.
4) Copy contents from temporary table back into column

This is assuming none of the contents is actually Strings, if there are any none Integers parts of the copy will fail.
Wushutwist

 

[tlbroadbent]

You can alter a column using T-SQL if you have SQL 7 or SQL 2000.

ALTER TABLE users ALTER COLUMN ch_1_year INT

You can also use Enterprise Manager to alter columns.

Be aware of the following limitations when altering columns.

ALTER COLUMN is not allowed if the compatibility level is 65 or earlier. For more information, see sp_dbcmptlevel.

The altered column cannot be: [ul][li]A column with a text, image, ntext, or timestamp data type.
[li]The ROWGUIDCOL for the table.
[li]A computed column or used in a computed column.
[li]A replicated column.
[li]Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or larger than the old size.
[li]Used in statistics generated by the CREATE STATISTICS statement. First remove the statistics using the DROP STATISTICS statement. Statistics automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.
[li]Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.
[li]Used in a CHECK or UNIQUE constraint, except that altering the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.
[li]Associated with a default, except that changing the length, precision, or scale of a column is allowed if the data type is not changed.[/ul] Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.