查找 SQL 临时表的数据类型

问题描述

我需要从使用#temp 表切换到@table 变量，以便我可以在函数中使用它.

I need to switch from using a #temp table to a @table variable so that I can use it in a function.

我的查询使用 insert into #temp(来自多个表)，如下所示:

My query uses insert into #temp (from multiple tables) like so:

SELECT
  a.col1,
  a.col2,
  b.col1...
INTO #temp
FROM ...

是否有一种简单的方法可以找出#temp 表中列的数据类型，以便我可以创建与#temp 具有相同列和数据类型的@table 变量?

Is there an easy way to find out the data types of the columns in the #temp table so that I can create the @table variable with the same columns and data types as #temp?

推荐答案

您需要确保 sp_help 在表所在的同一数据库 (tempdb) 中运行.您可以通过直接为调用添加前缀来实现:

You need to make sure sp_help runs in the same database where the table is located (tempdb). You can do this by prefixing the call directly:

EXEC tempdb.dbo.sp_help @objname = N'#temp';

或者通过在 tempdb.sys.columns 前添加连接前缀:

Or by prefixing a join against tempdb.sys.columns:

SELECT [column] = c.name,
       [type] = t.name, c.max_length, c.precision, c.scale, c.is_nullable
    FROM tempdb.sys.columns AS c
    INNER JOIN tempdb.sys.types AS t
    ON c.system_type_id = t.system_type_id
    AND t.system_type_id = t.user_type_id
    WHERE [object_id] = OBJECT_ID(N'tempdb.dbo.#temp');

这对您来说并没有什么好处，比如调整 varchar 的 max_length 与 nvarchar 不同，但这是一个好的开始.

This doesn't handle nice things for you, like adjusting max_length for varchar differently from nvarchar, but it's a good start.

在 SQL Server 2012 或更高版本中，您可以使用新的 DMF 来描述结果集，这可以解决这个问题(并为您组装 max_length/precision/scale).但它不支持#temp 表，所以只需注入查询而没有 INTO:

In SQL Server 2012 or better, you can use a new DMF to describe a resultset, which takes that issue away (and also assembles max_length/precision/scale for you). But it doesn't support #temp tables, so just inject the query without the INTO:

SELECT name, system_type_name, is_nullable
  FROM sys.dm_exec_describe_first_result_set(N'SELECT
        a.col1,
        a.col2,
        b.col1...
      --INTO #temp
      FROM ...;',NULL,1);

这篇关于查找 SQL 临时表的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！