converting sql_variant column type to int

Hi,
I've got a table which has got a sql_variant column (sql server instance is 2000). the current data in that column are all numbers so can be converted to int.
I want to change the column type from sql_variant to int for that column. I have tried

alter table mytable alter column mycolumn int

and it complains
Disallowed implicit conversion from data type sql_variant to data type int, table 'mydatabase.dbo.mytable ', column 'mycolumn '. Use the CONVERT function to run this query.

I can do this task using EM but what that does is creating a new table temp table with int column type transferring data , dropping the original table and renaming the temp table to initial name.

therefore I need T-SQL command
Thanks

Really, I think I would suggest the same thing that you describe the EM doing -- drop the constraints on the original table, create the new table, copy in the data and then drop the old table. Can we get second and third opinions here?|||problem: this is template for a table in production server, which can not be dropped and also there are many constraints that points to this table. so using EM is not an option.

|||I would start by scripting out the table from the EM and massaging the generated script. Again, I'm not sure that you are going to be able to get out of creating a new version of the table and I would like additional opinions on this.|||

See if this helps.

create table dbo.t1 (

c1 sql_variant null

)

go

insert into dbo.t1 values(1)

go

alter table dbo.t1

add c2 int

go

update dbo.t1

set c2 = cast(c1 as int)

go

alter table dbo.t1

drop column c1

go

exec sp_rename 'dbo.t1.c2', 'c1', 'COLUMN'

go

select *

from dbo.t1

go

drop table dbo.t1

go

If there are indexes or constraints referencing column [c1], you have to drop them first and recreating them after renaming new column.

AMB

|||good solution, but column order is important for me. I think I can got to syscolumn table and change the column order there but this won't work on 2005|||

Kolf,

> but column order is important for me

Sorry about it, that is the beauty about relational dbs, the position of the column is not important at all. If that is the case, there is not other option than creating a new table, move all data, drop constraints refrencing the table, drop old table, recreate constraints and indexes. That is what" Enterprise Manager" or "Management Studio" does.

AMB