sql查询数据库表字段的字段类型，操作字段，修改字段名

1、查询字段类型

select 'baipeishunniubi' as bnb,b.name as tablename,a.name as columnname,c.name as typename,a.max_length as typelength

from sys.columns a inner join sys.tables b on b.object_id=a.object_id

inner join sys.types c on c.system_type_id=a.system_type_id

where b.name='表名' and a.name='需要查询字段'

sp_columns 表名

SELECT (case when a.colorder=1 then d.name else null end) 表名,
a.colorder 字段序号,
a.name 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
 (case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in   (SELECT indid FROM sysindexkeys  WHERE (id = a.id) AND (colid in  (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))  AND (xtype = 'PK'))>0 then '√' else '' end) 主键,
 b.name 类型,
 a.length 占用字节数,  
 COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,  
 isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
 (case when a.isnullable=1 then '√'else '' end) 允许空,  
 isnull(e.text,'') 默认值,
 isnull(g.[value], ' ') AS [说明] 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' 
 left join syscomments e on a.cdefault=e.id  
 left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id 
 left join sys.extended_properties f on d.id=f.class and f.minor_id=0 
 where b.name is not null and d.name='表名' order by a.id,a.colorder 

2、操作字段

修改：

alter table 表名 alter column 需要修改的字段名 需要修改的数据类型

例如：alter table t_user alter column information nvarchar(200)

新增字段设置默认值：

alter table 表名 add  column 列名 数据类型 default 默认值 

例如：alter table t_user add  column information int(4)  default 0

新增字段设置非空：

alter table 表名 add 字段名 字段属性 是否允许为空

例如：alter table test_info add 'name' varchar(256) not null comment '名称'

删除字段：

alter table 表名  drop column 字段名 

例如：alter table test_info drop column name

3、修改字段名

exec sp_rename '表名点原字段名','新字段名'

例如：exec sp_rename 't_user.information','info'