1 SELECT
2 columnTableName = case when a.colorder=1 then d.name else '' end, --表名
3 columnTableDes = case when a.colorder=1 then isnull(f.value,'') else '' end, --表说明
4 columnNo = a.colorder, --字段序号
5 columnName = a.name, --字段名
6 columnId = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end, --标识
7 coulmnIsKey = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
8 SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end, -- 主键
9 columnType = b.name, --类型
10 coulmnByte = a.length, --占用字节数
11 columnLength = COLUMNPROPERTY(a.id,a.name,'PRECISION'), --长度
12 columnPoint = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), --小数位数
13 coulmnIsnull = case when a.isnullable=1 then '√'else '' end, --允许空
14 columnDefault = isnull(e.text,''), --默认值
15 columnDesc = isnull(g.[value],'') --字段说明
16 FROM
17 syscolumns a
18 left join
19 systypes b
20 on
21 a.xusertype=b.xusertype
22 inner join
23 sysobjects d
24 on
25 a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
26 left join
27 syscomments e
28 on
29 a.cdefault=e.id
30 left join
31 sys.extended_properties g
32 on
33 a.id=G.major_id and a.colid=g.minor_id
34 left join
35 sys.extended_properties f
36 on
37 d.id=f.major_id and f.minor_id=0
38 where
39 d.name='commenTable' --如果只查询指定表,加上此红色where条件，tablename是要查询的表名；去除红色where条件查询说有的表信息
40 order by
41 a.id,a.colorder
