如何查看临时表#foo的所有列名？含存储过程适配需求

问题根源 #

SQL Server中，局部临时表（以#开头）在tempdb中的实际名称会自动添加下划线和随机后缀（比如你看到的foo_____________________________a1b4），这是系统为了区分不同会话中同名的临时表。所以直接用tbl.name = ''#foo'''作为查询条件，根本匹配不到实际存在的表，自然返回空结果。

硬编码查询临时表列名的修正方案 #

不需要纠结临时表的实际后缀，直接通过OBJECT_ID获取当前会话中临时表的对象ID，以此作为查询条件最可靠：

Declare @sql nvarchar(max)
Declare @tempTableId int = OBJECT_ID('tempdb..#foo')

Set @sql='SELECT 
            c.name AS FieldName,
            t.name AS DataType
        FROM 
            tempdb.sys.columns c
        INNER JOIN 
            tempdb.sys.types t ON c.user_type_id = t.user_type_id
        WHERE 
            c.object_id = ' + CAST(@tempTableId AS nvarchar(20))

Exec(@sql)

OBJECT_ID('tempdb..#foo')会精准返回当前会话中#foo临时表的对象ID，完全不受后缀影响。

通用存储过程实现 #

要做成支持任意临时表的存储过程，核心还是利用OBJECT_ID来定位目标表，同时处理传入参数的两种形式（带#或不带#）：

CREATE PROCEDURE GetTempTableColumns
    @tempTableName nvarchar(128) -- 支持传入'foo'或'#foo'格式的临时表名
AS
BEGIN
    SET NOCOUNT ON;

    -- 确定临时表的对象ID
    DECLARE @tempTableId int
    IF LEFT(@tempTableName, 1) = '#'
        SET @tempTableId = OBJECT_ID('tempdb..' + @tempTableName)
    ELSE
        SET @tempTableId = OBJECT_ID('tempdb..#' + @tempTableName)

    -- 校验临时表是否存在
    IF @tempTableId IS NULL
    BEGIN
        RAISERROR('指定的临时表不存在', 16, 1)
        RETURN
    END

    -- 动态生成查询语句并执行
    DECLARE @sql nvarchar(max)
    SET @sql = N'SELECT 
                    c.name AS FieldName,
                    t.name AS DataType,
                    c.max_length AS MaxLength,
                    c.is_nullable AS IsNullable
                FROM 
                    tempdb.sys.columns c
                INNER JOIN 
                    tempdb.sys.types t ON c.user_type_id = t.user_type_id
                WHERE 
                    c.object_id = ' + CAST(@tempTableId AS nvarchar(20))

    EXEC sp_executesql @sql
END

使用示例：

-- 假设已创建#foo临时表
EXEC GetTempTableColumns 'foo'
-- 或
EXEC GetTempTableColumns '#foo'

补充说明 #

• 用OBJECT_ID的方式天然支持会话隔离：不同会话中的同名临时表不会互相干扰，因为每个会话的临时表对象ID是唯一的。
• 若非要用名称匹配，可使用tbl.name LIKE 'foo[_]%'，但必须结合sys.dm_db_session_space_usage关联当前会话ID（@@SPID）来过滤，这种方式比OBJECT_ID复杂得多，不推荐。

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