查 sys.columns 关联 sys.types 能获取最准确的类型信息,因 sys.columns 仅存 type_id,需 JOIN 获取 varchar 等实际类型名,且应使用 user_type_id 关联并优先取 t.name,避免硬编码 system_type_id 映射。

查 sys.columns + sys.types 能拿到最准确的类型信息
SQL Server 的列数据类型在 sys.columns 里只存 type_id,不直接存类型名;必须关联 sys.types 才能拿到像 varchar、datetime2 这样的实际类型名。忽略这步,容易误读成 user_type_id 对应的系统编号。
实操建议:
- 用
JOIN sys.types ON c.user_type_id = t.user_type_id(不是system_type_id),因为用户自定义类型(如别名类型、CLR 类型)需走user_type_id - 优先选
t.name而非c.system_type_id对应的硬编码映射,避免遗漏新版本类型(如 SQL Server 2016+ 的datetimeoffset) - 若要区分“基础类型”和“用户定义类型”,可加
CASE WHEN t.is_user_defined = 1 THEN 'UDT' ELSE 'system' END
sp_help 不适合自动化,但适合快速人工查看
sp_help 'TableName' 会返回多结果集:第一张是列信息(含类型、长度、是否为空),第二张是索引,第三张是约束……对脚本或程序调用极不友好,且输出列名不固定(比如长度字段叫 Length 还是 max_length 看版本),容易解析出错。
适用场景:
- SSMS 里临时查某张表,按 F4 或右键 → “查看依赖关系”后顺手敲一下
- 需要同时看默认值、标识属性、计算列公式时,它比手工拼
sys视图快 - 注意:
sp_help返回的Length列对varchar是字节数,对nvarchar是字符数 × 2 —— 实际业务中容易误判存储容量
INFORMATION_SCHEMA.COLUMNS 只返回标准 SQL 类型名
这个视图把所有 SQL Server 特有类型都“降级”成通用名:比如 datetime2(7) 显示为 datetime2(丢了精度),varchar(50) 和 varchar(max) 都显示为 character varying,decimal(18,2) 变成 decimal(没了标度信息)。
所以:
- 跨数据库迁移(比如导到 PostgreSQL)时可用,因为它遵循 ANSI 标准
- 做内部元数据管理、生成建表语句、校验类型一致性时,千万别依赖它 —— 你会漏掉
(max)、精度、排序规则等关键细节 -
CHARACTER_MAXIMUM_LENGTH对text/ntext返回-1,但对varchar(max)返回-1同样成立,无法区分二者
带长度/精度的完整类型字符串得自己拼
SQL Server 没有内置函数直接返回 varchar(50) 或 decimal(18,2) 这种带括号参数的完整类型名。必须根据 sys.types 的 name,再结合 sys.columns 的 max_length、precision、scale 字段手动组装。
关键逻辑:
-
varchar/char/varbinary:用max_length,注意max_length = -1表示(max) -
nvarchar/nchar:max_length是字节数,要除以 2 才是字符数(但max_length = -1仍对应(max)) -
decimal/numeric:拼precision和scale,如decimal(18,2) -
datetime2/time/datetimeoffset:看scale字段(0–7),拼成datetime2(3)
真正麻烦的是排序规则(collation)、是否为计算列、是否加密 —— 这些不会出现在类型字符串里,但影响实际行为。如果只查“类型”,很容易以为事情结束了。










