sqlserver 查询数据库表列字段,中文对应

最新推荐文章于 2023-03-26 13:31:48 发布

原创最新推荐文章于 2023-03-26 13:31:48 发布 · 3.5k 阅读

CC 4.0 BY-SA版权

该博客围绕SQL Server展开，主要讲述查询数据库列字段并实现中文对应的相关内容，属于信息技术中后端开发数据库操作方面的知识。

 SELECT 
表名 =case when a.colorder=1then d.name else''end,
表说明 =case when a.colorder=1then isnull(f.value,'') else''end,
字段序号 = a.colorder,
字段名 = a.name,
标识 =case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1then'√'else''end,
主键 =case when exists(SELECT 1 FROM sysobjects where xtype='PK'and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then'√'else''end,
外键 =case when exists(SELECT 1 FROM sysforeignkeys WHERE sysforeignkeys.fkeyid = d.id and sysforeignkeys.fkey=a.colid) then'√'else''end,
 类型 = b.name,
 占用字节数 = a.length,
 长度 =COLUMNPROPERTY(a.id,a.name,'PRECISION'),
 小数位数 =isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
 允许空 =case when a.isnullable=1then'√'else''end,
 默认值 =isnull(e.text,''),
 字段说明 =isnull(g.[value],'')
 FROM 
 syscolumns a
 left join 
 systypes b 
 on 
 a.xusertype=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.major_id and f.minor_id=0
 where 
 d.name='B_Order'--这里为要查询的表名
 order by 
 a.id,a.colorder