SQL查询表结构-优快云博客

本文链接：https://blog.youkuaiyun.com/terryhuang/article/details/1569290

本文提供了一个详细的SQL查询语句，用于获取指定表的完整结构信息，包括字段名、字段类型、是否为主键等属性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL查询表结构的语句

select
( case when a.colorder = 1 then d.name else '' 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 sysproperties g
on a.id = g.id and a.colid = g.smallid