查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,'PRECISION'),????
????????小数位数=isnull(COLUMNPROPERTY(a.id,'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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!