sql server - 查询表结构
方式一:显示多种信息
sp_help table_name
方式二:只显示字段
sp_columns table_name
方式三:自定义
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' 即可。
方式四:自定义
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:
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