获取表中字段信息(主外键，字段名，数据类型，字段长度，列说明)

本文链接：https://blog.youkuaiyun.com/wangfeng8317/article/details/2079637

本文提供了一种使用SQL Server查询表结构的方法，包括字段名称、数据类型、字段长度及字段说明等信息。此外还介绍了如何单独获取表的主键、外键及所有字段的详细信息。

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

select
(case when PKeyCol.COLUMN_NAME is null then '' else 'PK' end) +
(case when KeyCol2.COLUMN_NAME is null then ''
when NOT PKeyCol.COLUMN_NAME is null then ',FK' else 'FK' end) as [主/外键],
col.COLUMN_NAME as 字段名称,
DATA_TYPE as DataType,
(case when CHARACTER_MAXIMUM_LENGTH is null then '' else CAST(CHARACTER_MAXIMUM_LENGTH as varchar(50)) end) as 字段长度,
--(case when coldesc.value is null then '' else coldesc.value end) AS 字段说明,*
ISNULL (CAST(coldesc.[value] AS nvarchar(50)) , '') AS 字段说明
from INFORMATION_SCHEMA.COLUMNS as col
LEFT OUTER JOIN
(select COLUMN_NAME,TABLE_NAME FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyCol
LEFT OUTER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RefCol ON
KeyCol.CONSTRAINT_CATALOG=RefCol.CONSTRAINT_CATALOG AND
KeyCol.CONSTRAINT_NAME=RefCol.CONSTRAINT_NAME
WHERE RefCol.CONSTRAINT_NAME IS NULL) PKeyCol
ON PKeyCol.COLUMN_NAME=Col.COLUMN_NAME AND PKeyCol.TABLE_NAME=Col.TABLE_NAME
LEFT OUTER JOIN
(INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyCol2
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RefCol2 ON
KeyCol2.CONSTRAINT_CATALOG=RefCol2.CONSTRAINT_CATALOG AND
KeyCol2.CONSTRAINT_NAME=RefCol2.CONSTRAINT_NAME)
ON KeyCol2.COLUMN_NAME=Col.COLUMN_NAME AND KeyCol2.TABLE_NAME=Col.TABLE_NAME
LEFT OUTER JOIN ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', "+tableName+", 'column', default) as coldesc
ON col.COLUMN_NAME = coldesc.objname COLLATE Chinese_PRC_CI_AS
where col.TABLE_NAME="+tableName+"

将"+tableName+"换成你要查询的表名就可以了

获取表中所有信息,没有字段说明

exec sp_columns " + tableName + "

获取表中主键的字段

select COLUMN_NAME AS KeyName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyCol LEFT OUTER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RefCol ON KeyCol.CONSTRAINT_CATALOG=RefCol.CONSTRAINT_CATALOG AND KeyCol.CONSTRAINT_NAME=RefCol.CONSTRAINT_NAME WHERE RefCol.CONSTRAINT_NAME IS NULL AND KeyCol.TABLE_NAME="+tableName+"

获取表中字段的信息（列名称，数据类型，长度，字段说明）

                "select col.COLUMN_NAME as FieldName,DATA_TYPE as DataType,(case when CHARACTER_MAXIMUM_LENGTH is null then '' else CAST(CHARACTER_MAXIMUM_LENGTH as varchar(50)) end) as FieldLength,ISNULL (CAST(coldesc.[value] AS nvarchar(50)) , '') AS FieldExplain from INFORMATION_SCHEMA.COLUMNS as col LEFT OUTER JOIN ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', " +
                tableName +
                ", 'column', default) as coldesc ON col.COLUMN_NAME = coldesc.objname COLLATE Chinese_PRC_CI_AS where col.TABLE_NAME=" +
                tableName + ""