将sql数据库字段char全部转换成varchar

--删除UQ约束

declare @string varchar(8000)

while exists(select name from sysobjects where xtype='UQ')

begin

 select top 1 @string= 'alter table ' +b.name+ ' drop constraint ' +a.name

  from (select parent_obj,name from sysobjects where xtype='UQ' ) a,

        (select id,name from sysobjects where OBJECTPROPERTY(id, N'IsUserTable')  = 1 ) b

    where a.parent_obj=b.id

 exec(@string)

end

go 

--默认值或 default 约束

declare  tb  cursor  for         

select sql = 'ALTER  TABLE ' +  t3.name   + ' DROP  CONSTRAINT ' +t4.name  +';' 

  from   syscolumns   t1,syscomments   t2,sysobjects   t3   ,sysobjects   t4   

    where     t1.cdefault=t2.id   and   t3.xtype='u'   and   t3.id=t1.id   

  and   t4.xtype='d'   and   t4.id=t2.id --and t1.length > 1 and t1.xtype = 175 

 

declare  @sql  varchar(1000)  

open  tb  

fetch  next  from  tb  into  @sql  

while  @@fetch_status  =  0  

begin  

           exec(@sql)  

           fetch  next  from  tb  into  @sql  

end  

close  tb  

deallocate  tb  

--默认值或 default 约束

--删除所有的外键

declare @string varchar(8000)

while exists(select name from sysobjects where type='F'and name not like 'FK__ML%')

begin

 select top 1 @string= 'alter table ' +b.name+ ' drop constraint ' +a.name

  from (select parent_obj,name from sysobjects where type='F' and name not like 'FK__ML%') a,

        (select id,name from sysobjects where OBJECTPROPERTY(id, N'IsUserTable')  = 1 and name not like 'FK__ML%') b

    where a.parent_obj=b.id

 exec(@string)

end

go 

--索引

declare @string varchar(8000)

while exists(

 select table_name= o.name,index_name= x.name 

  from sysobjects o, sysindexes x, syscolumns c, sysindexkeys xk

  where o.type in ('u') 

  and convert(bit,(x.status & 0x800)/0x800)=0

  and x.id = o.id

  and o.id = c.id

  and o.id = xk.id

  and x.indid = xk.indid

  and c.colid = xk.colid

  and xk.keyno <= x.keycnt

  and permissions(o.id, c.name) <> 0

  and     (x.status&32) = 0  -- no hypothetical indexes

  and o.name not like 'pbc%'

  and o.name not like 'ml_%'

  group by o.name,x.name)

begin

select top 1 @string= 'drop index ' +o.name+'.'+ x.name 

  from sysobjects o, sysindexes x, syscolumns c, sysindexkeys xk

  where o.type in ('u') 

  and convert(bit,(x.status & 0x800)/0x800)=0

  and x.id = o.id

  and o.id = c.id

  and o.id = xk.id

  and x.indid = xk.indid

  and c.colid = xk.colid

  and xk.keyno <= x.keycnt

  and permissions(o.id, c.name) <> 0

  and     (x.status&32) = 0  -- no hypothetical indexes

  and o.name not like 'pbc%'

  and o.name not like 'ml_%'

  group by o.name,x.name

exec(@string)

end

go

--primary key 约束

declare @string varchar(8000)

while exists(select name from sysobjects where xtype='PK' AND name not like 'PK__ml%' and name not like 'pbc%'  )

begin

 select top 1 @string='alter table ' +b.name+ ' drop constraint ' +a.name

  from (select parent_obj,name from sysobjects where xtype='PK'AND name not like 'PK__ml%' and name not like 'pbc%' ) a,

        (select id,name from sysobjects where objectproperty(id, N'isusertable') = 1 AND name not like 'PK__ml%' and name not like 'pbc%' ) b

    where a.parent_obj=b.id

 exec(@string)

end

go

declare  tb  cursor  for         

select   sql=

case a.isnullable when 0 then

'alter  table  ['+d.name +']  alter  column  ['+a.name+']'+' varchar(' + cast(a.length as varchar) +')' + 'not null'

else

'alter  table  ['+d.name +']  alter  column  ['+a.name+']'+' varchar(' + cast(a.length as varchar) +')'   

end

from  syscolumns  a  

           left  join  systypes  b  on  a.xtype=b.xusertype  

           inner  join  sysobjects  d  on  a.id=d.id    and  d.xtype='U'  and    d.name<>'dtproperties'  and d.name not like 'pbc%'

and d.name not like 'ml%'

where    

           b.name  ='char'  

           and a.length > 1 and   

           not  exists(select  1  from  sysobjects  where  xtype='PK'  and  name  in  (  

                       select  name  from  sysindexes  where  indid  in(  

                                   select  indid  from  sysindexkeys  where  id  =  a.id  and  colid=a.colid  

                       )))                        --主键不能修改  

order  by  d.name,a.name  

 

declare  @sql  varchar(1000)  

open  tb  

fetch  next  from  tb  into  @sql  

while  @@fetch_status  =  0  

begin  

           exec(@sql)  

           fetch  next  from  tb  into  @sql  

end  

close  tb  

deallocate  tb  

--去除所有varchar右空格

declare  tb  cursor  for         

select   sql=

'update  '+d.name +' set  '+a.name +'= rtrim(' + a.name +')'

from  syscolumns  a  

           left  join  systypes  b  on  a.xtype=b.xusertype  

           inner  join  sysobjects  d  on  a.id=d.id    and  d.xtype='U'  and    d.name<>'dtproperties'  

where    

           b.name  ='varchar'  and a.length > 1   

order  by  d.name,a.name  

 

declare  @sql  varchar(1000)  

open  tb  

fetch  next  from  tb  into  @sql  

while  @@fetch_status  =  0  

begin  

           exec(@sql)  

           fetch  next  from  tb  into  @sql  

end  

close  tb  

deallocate  tb  

--最后需重新创建主键外键DEFAULT