如何通过INFORMATION_SCHEMA视图在SQL存储过程中高效获取数据库元数据？

本文共计1102个文字，预计阅读时间需要5分钟。

直接回答：

为什么优先用 INFORMATION_SCHEMA.TABLES 和 COLUMNS？

因为它是 ANSI SQL 标准定义的，跨数据库兼容性好（SQL Server、PostgreSQL、MySQL 均支持基本结构），语义清晰、权限要求低（普通用户通常有读取权限），不像 sys.tables 那样依赖 DBA 权限或系统视图访问控制。

但它不返回 SQL Server 特有的字段，比如 is_identity、is_computed、collation_name（部分版本返回但不可靠）、max_length 的实际字节值（character_maximum_length 对 nvarchar 返回的是字符数而非字节数）。

• INFORMATION_SCHEMA.TABLES 只包含 TABLE_SCHEMA、TABLE_NAME、TABLE_TYPE，没有创建时间、行数、是否临时表等
• INFORMATION_SCHEMA.COLUMNS 中 data_type 是简化名（如 int、varchar），不区分 tinyint 和 smallint；numeric_precision 和 numeric_scale 对非数值类型为 NULL
• 它不暴露任何系统对象（如以 sys、dm_ 开头的视图），也不包含扩展属性（fn_listextendedproperty 无法通过它调用）

在存储过程中安全引用 INFORMATION_SCHEMA 视图的关键写法

不能直接拼接表名进 WHERE 条件——容易被注入，也不能硬编码数据库名，因为 INFORMATION_SCHEMA 视图**始终只作用于当前数据库上下文**。

正确做法是：先用 OBJECT_ID() 或 DB_ID() 校验输入合法性，再用参数化方式过滤。例如要查某张表的所有列：

CREATE PROCEDURE usp_GetColumnList @TableName NVARCHAR(128) AS BEGIN IF OBJECT_ID(@TableName, 'U') IS NULL BEGIN RAISERROR('Table "%s" does not exist or is not a user table.', 16, 1, @TableName); RETURN; END <pre class='brush:php;toolbar:false;'>SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = SCHEMA_NAME(); -- 默认取当前执行上下文的 schema，避免跨 schema 漏查

END

• 必须校验 @TableName 是否真实存在，否则空结果集不报错，容易误判
• SCHEMA_NAME() 返回当前会话默认 schema（通常是 dbo），但若调用者显式指定 schema.table（如 sales.Orders），需拆分解析——INFORMATION_SCHEMA 要求 TABLE_SCHEMA 和 TABLE_NAME 分开传入
• 不要用 EXEC('SELECT ... FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''' + @TableName + '''') ——字符串拼接等于放弃安全边界

INFORMATION_SCHEMA 和 sys 视图混用时的常见坑

很多人想“补全” INFORMATION_SCHEMA 缺失的字段（比如加 is_identity），于是 JOIN sys.columns。这看似合理，实则危险：

• JOIN 条件易出错：INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION ≠ sys.columns.column_id（前者从 1 开始连续编号，后者可因 DROP/ADD 列而产生空洞）
• 权限不一致：用户可能有 INFORMATION_SCHEMA 读权限，但无权查 sys.columns，导致运行时报错 SELECT permission denied on object 'columns', database 'mssqlsystemresource', schema 'sys'
• 数据库上下文错位：在跨库查询中，INFORMATION_SCHEMA 仍指向当前 DB，而 sys.columns 若未加三段式引用（otherdb.sys.columns），就查错了地方

真需要额外字段，应改用纯 sys 视图方案，或用 COLUMNPROPERTY(object_id(@TableName), column_name, 'IsIdentity') 单独补查——它比 JOIN 更轻量、更可控。

什么时候不该用 INFORMATION_SCHEMA？

当你要获取以下任一信息时，INFORMATION_SCHEMA 就不够用了，必须转向 sys 视图或系统存储过程：

• 索引定义（sp_statistics 或 sys.indexes + sys.index_columns）
• 主键/外键约束详情（sp_pkeys / sp_fkeys 或 sys.key_constraints）
• 列的默认值表达式（sys.columns.default_object_id → sys.objects → sys.sql_modules）
• 统计信息最后更新时间（sys.dm_db_stats_properties）
• 扩展属性（fn_listextendedproperty）

这些能力不是“增强版”，而是根本不在标准范围里——INFORMATION_SCHEMA 的设计目标就是轻量、稳定、可移植，不是功能完备。