sql server类型&&批量修改字段类型

—查看数据库支持的类型
SELECT * FROM systypes

—查看列名
SELECT * FROM syscolumns

-----批量修改数据库某字段数据类型

—修改loginip这个字段的类型varchar(MAX)到varchar(255)
SELECT sql=‘alter table [’+d.name
+‘] alter column [’+a.name+‘] ’
+b.name+’(255);’
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 in(‘varchar(MAX)’)
A.name IN(‘LOGINIP’)

----修改upload这个字段的类型为默认的加255

SELECT sql=‘alter table [’+d.name
+‘] alter column [’+a.name+‘] ’
+b.name+’(255);’
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
A.name IN(‘upload0’)