SQL Server查询表结构
标签：object ide property entity xtend dex nbsp color 标识
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did117024
SQL Server查询表结构
( 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 d.name = ‘ BUS_TransitSite ‘ -- 如果只查询指定表,加上此条件
order by a.id,a.colorder
阅读：27次