如何用SQL语句或存储过程实现一个表中字段的数据类型由TEXT变为CHAR

[wcq]

如题

[txlicenhe]

eg:
alter table yourtable alter column cname varchar(100)

[wcq]

不行,因为用alter column 的字段数据类型不能为TEXT类型

[pengdali]

alter table tablename add aaa1 text

update tablename set aaa1=aaa

alter table tablename drop column aaa

exec sp_rename 'tablename.aaa1','aaa'

[CrazyFor]

alter table tablename add aaa1 varchar(8000)

update tablename set aaa1=substring(aaa,1,8000)

alter table tablename drop column aaa

exec sp_rename 'tablename.aaa1','aaa'

[txlicenhe]

alter table tablename add aaa1 varchar(8000)

update tablename set aaa1=left(aaa,8000)

alter table tablename drop column aaa

exec sp_rename 'tablename.aaa1','aaa',column

[pengdali]

alter table tablename add aaa1 char(8000)

update tablename set aaa1=substring(aaa,1,8000)

alter table tablename drop column aaa

exec sp_rename 'tablename.aaa1','aaa'

[zjcxc]

--修改列名
exec sp_rename '表名.字段名','字段名_bak','column'
go
--转换数据
alter table 表名 add 字段名 varchar(8000)
update 表名 set 字段名=cast(字段名_bak as varchar(8000))
alter table 表名 drop column 字段名_bak
select * from tb
drop table tb

[zjcxc]

例子:

create table tb(a text)
insert into tb values('abc')
exec sp_rename 'tb.a','b','column'
go
alter table tb add a varchar(8000)
update tb set a=cast(b as varchar(8000))
alter table tb drop column b
select * from tb
drop table tb