declare @name varchar(100)
declare @type varchar(100)
declare @len varchar(100)
declare @table varchar(100)
declare @sql varchar(500)
set @table = 'a1'
while exists (select A.name, C.name, A.length from syscolumns A, sysobjects B, systypes C
where A.id = B.id and A.xtype = C.xtype and B.name = @table and C.name = 'char')
begin
select top 1 @name = A.name, @type = C.name, @len = A.length
from syscolumns A, sysobjects B, systypes C
where A.id = B.id and A.xtype = C.xtype and B.name = @table and C.name = 'char'
set @sql = 'alter table '+@table+' alter column '+@name+' varchar('+@len+')'
exec (@sql)
end
declare @type varchar(100)
declare @len varchar(100)
declare @table varchar(100)
declare @sql varchar(500)
set @table = 'a1'
while exists (select A.name, C.name, A.length from syscolumns A, sysobjects B, systypes C
where A.id = B.id and A.xtype = C.xtype and B.name = @table and C.name = 'char')
begin
select top 1 @name = A.name, @type = C.name, @len = A.length
from syscolumns A, sysobjects B, systypes C
where A.id = B.id and A.xtype = C.xtype and B.name = @table and C.name = 'char'
set @sql = 'alter table '+@table+' alter column '+@name+' varchar('+@len+')'
exec (@sql)
end
while exists (
select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE='char'
)
begin
select top 1 @table_name=TABLE_name,@column_name=column_name,@column_length = CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE='char'
exec('alter table ' + @table_name + ' alter column ' + @column_name + ' varchar(' + @column_length + ')')
end
go
update syscolumns set xusertype=167 where xusertype=175
go
exec sp_configure N'allow updates',0
from sysobjects a,syscolumns b,systypes c
where a.xtype='U' and a.Name<>'dtproperties'
and a.id=b.id and b.xtype=c.xtype and c.Name='Char'
Open Cu_A
Fetch Cu_A into @TableName,@ColumnName,@type,@Length
while @@Fetch_Status=0
begin
Exec('Alter table '+@TableName+' ALTER COLUMN '+@ColumnName+' varChar('+@Length+')')
Fetch Cu_A into @TableName,@ColumnName,@type,@Length
end
Close Cu_A
Deallocate Cu_A
from syscolumns where xtype=175 and objectproperty(id,'IsUserTable')=1
open #tb--数据修改前开启事务
begin tran
fetch next from #tb into @tbname,@fdname,@len
while @@fetch_status=0
begin
set @sql='alter table ['+@tbname+'] alter column ['+@fdname+'] varchar('+@len+')'
print @sql
exec(@sql)
fetch next from #tb into @tbname,@fdname,@len
end--先执行一次,测试无误后,将下面的回滚事务改为提交事务,以免错误
rollback tranclose #tb
deallocate #tb