sql server - 查询表结构

 方式一：显示多种信息

 sp_help table_name

 方式二：只显示字段

 sp_columns table_name

 方式三：自定义

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECT           SO.name 表名,      SC.name 表列名,      SC.colid 索引,      ST.name 类型  FROM             sysobjects   SO, -- 对象表      syscolumns   SC, -- 列名表      systypes     ST  -- 数据类型表   WHERE              SO.id = SC.id      AND   SO.xtype = 'U'                   -- 类型U表示表，V表示视图     AND   SO.status >= 0                  -- status >= 0 为非系统对象     AND   SC.xtype = ST.xusertype     AND   SO.name = 'table_name'  -- 某张特定表  ORDER BY        SO.name, SC.colorder         -- 按表名、列名排序

　　如果要查看视图信息，只需要将SO.xtype = 'U'该为SO.xtype = 'V' 即可。

 方式四：自定义

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

SELECT a.name AS 字段名     , 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 AS 主键, b.name AS 类型     , CASE         WHEN a.isnullable = 0 THEN '√'         ELSE ''     END AS 必填     , 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    where d.name='MP_GuaranteeMoneyApply'--所要查询的表  order by a.id,a.colorder

--方式4：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

SELECT a.name AS 字段名, b.name AS 类型, a.length, CASE         WHEN a.isnullable = 0 THEN '√'         ELSE ''     END AS 必填     , 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 AS 主键     , 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   where d.name='MP_SP_TP_ApproveAgeing'--所要查询的表  order by a.id,a.colorder