首页 > 代码库 > 查sqlserver表结构说明的方法
查sqlserver表结构说明的方法
一、查出某个库的所有表名
USE database_name GO SELECT tab.name AS tableName FROM sys.columns col INNER JOIN sys.tables tab ON col.object_id = tab.object_id LEFT JOIN sys.extended_properties per ON col.column_id = per.minor_id AND per.major_id = tab.object_id INNER JOIN sys.types type ON col.user_type_id = type.user_type_id group by tab.name ORDER BY tab.name
二、查出某个库的所有字段说明
USE database_name GO SELECT tab.name AS tableName, col.name AS column_name, per.value AS column_comment, col.is_identity AS column_key, type.name AS data_type, col.max_length AS column_length FROM sys.columns col INNER JOIN sys.tables tab ON col.object_id = tab.object_id LEFT JOIN sys.extended_properties per ON col.column_id = per.minor_id AND per.major_id = tab.object_id INNER JOIN sys.types type ON col.user_type_id = type.user_type_id ORDER BY tab.name, col.is_identity DESC
二、查出某个库的所有字段说明(查出数据详细格式化版)
USE database_name GO SELECT 表名=case when a.colorder=1 then d.name else ‘‘ end, 表说明=case when a.colorder=1 then isnull(f.value,‘‘) else ‘‘ end, 字段序号=a.colorder, 字段名=a.name, 标识=case when COLUMNPROPERTY( a.id,a.name,‘IsIdentity‘)=1 then ‘√‘else ‘‘ end, 主键=case when 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 ))) 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=1 then ‘√‘else ‘‘ end, 默认值=isnull(e.text,‘‘), 字段说明=isnull(g.[value],‘‘) 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.major_id and f.minor_id=0 order by a.id,a.colorder
本文出自 “风之痕_雪虎” 博客,请务必保留此出处http://snowtiger.blog.51cto.com/12931578/1929112
查sqlserver表结构说明的方法
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。