TSQL中如何获取已声明表变量的主键字段名称

实现原理 #

表变量的元数据实际存储在tempdb的系统视图中，和临时表的存储位置类似，只是表变量的命名格式为#加8位十六进制字符，且仅在当前会话、当前批处理中可见。

可直接运行的示例代码 #

示例1：查询单字段主键的表变量 #

-- 声明测试用表变量
DECLARE @Table1 TABLE (id INT PRIMARY KEY)

-- 主键查询语句
SELECT 
    col.name AS PrimaryKeyColumnName
FROM 
    tempdb.sys.tables t
INNER JOIN 
    tempdb.sys.indexes idx 
    ON t.object_id = idx.object_id 
    AND idx.is_primary_key = 1
INNER JOIN 
    tempdb.sys.index_columns idx_col 
    ON idx.object_id = idx_col.object_id 
    AND idx.index_id = idx_col.index_id
INNER JOIN 
    tempdb.sys.columns col 
    ON idx_col.object_id = col.object_id 
    AND idx_col.column_id = col.column_id
WHERE 
    -- 匹配表变量的命名格式
    t.name LIKE '#[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]'
    -- 取当前会话最新创建的表变量
    AND t.object_id = (
        SELECT MAX(object_id) 
        FROM tempdb.sys.tables 
        WHERE name LIKE '#[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]'
    )
ORDER BY 
    -- 联合主键按定义的顺序返回
    idx_col.key_ordinal

运行后返回结果为：id

示例2：查询联合主键的表变量 #

-- 声明联合主键测试表变量
DECLARE @Table2 TABLE (id1 INT, id2 INT, id3 INT, PRIMARY KEY(id1, id2, id3))

-- 主键查询语句和上面完全一致
SELECT 
    col.name AS PrimaryKeyColumnName
FROM 
    tempdb.sys.tables t
INNER JOIN 
    tempdb.sys.indexes idx 
    ON t.object_id = idx.object_id 
    AND idx.is_primary_key = 1
INNER JOIN 
    tempdb.sys.index_columns idx_col 
    ON idx.object_id = idx_col.object_id 
    AND idx.index_id = idx_col.index_id
INNER JOIN 
    tempdb.sys.columns col 
    ON idx_col.object_id = col.object_id 
    AND idx_col.column_id = col.column_id
WHERE 
    t.name LIKE '#[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]'
    AND t.object_id = (
        SELECT MAX(object_id) 
        FROM tempdb.sys.tables 
        WHERE name LIKE '#[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]'
    )
ORDER BY 
    idx_col.key_ordinal

运行后按顺序返回结果：id1、id2、id3

注意事项 #

• 查询必须和表变量的声明放在同一个批处理中运行，批处理结束后表变量的元数据会被自动清理，无法再查询到。
• 如果同一批处理中声明了多个表变量，需要在WHERE条件中补充字段数量、字段名、字段类型等过滤条件，精准匹配目标表变量即可。

内容的提问来源于stack exchange，提问作者user9947392