查找 SQL 临时表的数据类型

Finding the data types of a SQL temporary table

提问by woggles

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

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

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

我的查询使用 insert into #temp （来自多个表），如下所示：

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

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?

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

回答by Aaron Bertrand

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

您需要确保sp_help在表所在的同一数据库 (tempdb) 中运行。您可以通过直接为调用添加前缀来做到这一点：

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

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

或者通过为连接添加前缀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');

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

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

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:

在 SQL Server 2012 或更高版本中，您可以使用新的 DMF 来描述结果集，从而消除该问题（并为您组装 max_length/precision/scale）。但它不支持 #temp 表，所以只需注入没有 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);

回答by bill

The accepted answer does not give the data type.Joining tempdb.sys.columns with sys.types gives the data type as mentioned in the comment of the answer.But joining on system_type_id yields one extra row with datatype "sysname". Instead "user_type_id" gives the exact solution as given below.

接受的答案没有给出数据类型。将 tempdb.sys.columns 与 sys.types 连接给出了答案注释中提到的数据类型。但是在 system_type_id 上加入会产生一个数据类型为“sysname”的额外行。相反，“user_type_id”给出了如下给出的确切解决方案。

SELECT cols.NAME
 ,ty.NAME
FROM tempdb.sys.columns cols
JOIN sys.types ty ON cols.user_type_id = ty.user_type_id
WHERE object_id = OBJECT_ID('tempdb..#temp')

回答by FistOfFury

you need to qualify the sp_help process to run from the tempdb database to get details about a hash table, because that's where the hash table is actually stored. If you attempt to run sp_help from a different database you'll get an error that the table doesn't exist in that database.

您需要限定 sp_help 进程从 tempdb 数据库运行以获取有关散列表的详细信息，因为散列表实际存储在此处。如果您尝试从不同的数据库运行 sp_help，您将收到一个错误，指出该表不存在于该数据库中。

If your query is executing outside of tempdb, as I assume it is, you can run the following:

如果您的查询在 tempdb 之外执行，正如我所假设的那样，您可以运行以下命令：

exec tempdb..sp_help #temp

One benefit of this procedure is it includes a text description of the column datatypes for you. This makes it very easy to copy and paste into another query, e.g. if you're trying use the definition of a temp table to create a table variable.

此过程的一个好处是它为您包含了列数据类型的文本描述。这使得复制和粘贴到另一个查询变得非常容易，例如，如果您尝试使用临时表的定义来创建表变量。

You could find the same information in the Syscolumns table, but it will give you numeric indentifiers for the types which you'll have to map yourself. Using sp_help will save you a step.

您可以在 Syscolumns 表中找到相同的信息，但它会为您提供必须自己映射的类型的数字标识符。使用 sp_help 将为您节省一个步骤。

回答by Nick Painter

The other answers will give you the information that you need, but still require you to type it all out when you define the table variable.

其他答案将为您提供所需的信息，但仍要求您在定义表变量时将其全部输入。

The following TSQL will allow you to quickly generate the table variable's definition for any given table.

以下 TSQL 将允许您为任何给定表快速生成表变量的定义。

This can save you a lot of time instead of manually typing table definitions like:

这可以为您节省大量时间，而不是手动输入表定义，例如：

table(Field1Name nvarchar(4), Field2Name nvarchar(20), Field3Name int
, Field4Name numeric(28,12))

TSQL:

TSQL：

select top 10 * 
into #temp
from db.dbo.myTable



declare @tableName nvarchar(max)
set @tableName = '#temp'

use tempdb
declare @tmp table(val nvarchar(max))
insert into @tmp 
select case data_type   
    when 'binary' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + cast(CHARACTER_MAXIMUM_LENGTH AS nvarchar(max)) + ')'
    when 'char' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + cast(CHARACTER_MAXIMUM_LENGTH AS nvarchar(max)) + ')'
    when 'datetime2' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(DATETIME_PRECISION as nvarchar(max)) + ')'
    when 'datetimeoffset' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(DATETIME_PRECISION as nvarchar(max)) + ')'
    when 'decimal' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + cast(NUMERIC_PRECISION as nvarchar(max)) + ',' + cast(NUMERIC_SCALE as nvarchar(max)) + ')'
    when 'nchar' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + cast(CHARACTER_MAXIMUM_LENGTH AS nvarchar(max)) + ')'
    when 'numeric' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + cast(NUMERIC_PRECISION as nvarchar(max)) + ',' + cast(NUMERIC_SCALE as nvarchar(max)) + ')'
    when 'nvarchar' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + cast(CHARACTER_MAXIMUM_LENGTH AS nvarchar(max)) + ')'
    when 'time' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(DATETIME_PRECISION as nvarchar(max)) + ')'
    when 'varbinary' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + cast(CHARACTER_MAXIMUM_LENGTH AS nvarchar(max)) + ')'
    when 'varchar' then COLUMN_NAME + ' ' + DATA_TYPE + '(' + cast(CHARACTER_MAXIMUM_LENGTH AS nvarchar(max)) + ')'
    -- Most standard data types follow the pattern in the other section.  
    -- Non-standard datatypes include: binary, char, datetime2, datetimeoffset, decimal, nvchar, numeric, nvarchar, time, varbinary, and varchar
    else COLUMN_NAME + ' ' + DATA_TYPE

    end +  case when IS_NULLABLE <> 'YES' then ' NOT NULL' else '' end 'dataType'
     from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME like @tableName + '%'

declare @result nvarchar(max)
set @result = ''
select @result = @result + [val] + N','
from @tmp
where val is not null

set @result = substring(@result, 1, (LEN(@result)-1))

-- The following will replce '-1' with 'max' in order to properly handle nvarchar(max) columns
set @result = REPLACE(@result, '-1', 'max')
select @result

Output:

输出：

Field1Name nvarchar(4), Field2Name nvarchar(20), Field3Name int
, Field4Name numeric(28,12)

回答by Mohammad Shehroz

to get columns name with data type use this

要获取具有数据类型的列名，请使用它

EXEC tempdb.dbo.sp_help N'#temp';

or To get only columns name to use this

或仅获取列名称以使用此

SELECT * 
FROM tempdb.sys.columns 
WHERE [object_id] = OBJECT_ID(N'tempdb..#temp');

回答by gotqn

What you are trying to do is to get information about the system types of the columns you are querying.

您要做的是获取有关您正在查询的列的系统类型的信息。

For SQL Server 2012 and lateryou can use sys.dm_exec_describe_first_result_setfunction. It returns very detailed information about the columns and the system_type_columnholds the complete system type definition (ready to use in your table definition):

因为SQL Server 2012 and later你可以使用sys.dm_exec_describe_first_result_set函数。它返回有关列的非常详细的信息，并system_type_column保存完整的系统类型定义（准备在您的表定义中使用）：

For example:

例如：

SELECT * 
FROM [sys].[dm_exec_describe_first_result_set] (N'SELECT object_id, name, type_desc FROM sys.indexes', null, 0);

回答by Icarus

Yes, the data types of the temp table will be the data types of the columns you are selecting and inserting into it. So just look at the select statement and determine each data type based on the column you select.

是的，临时表的数据类型将是您选择并插入其中的列的数据类型。因此，只需查看 select 语句并根据您选择的列确定每种数据类型。

回答by billinkc

I'd go the lazy route and use

我会走懒惰的路线并使用

use tempdb
GO
EXECUTE sp_help #temp

回答by Arulmouzhi

Finding the data types of a SQL temporary table

查找 SQL 临时表的数据类型

METHOD 1 – Using SP_HELP

方法 1 – 使用 SP_HELP

EXEC TempDB..SP_HELP #TempTable;

Note-

笔记-

In the Table Structure, the Table Name shows something like ‘#TempTable__________________________________________________________________________________________________________0000000004CB'. Actually, the total length of each and every Temp Table name will be 128 . To handle the Same Temp Table name in Multiple Sessions differently, SQL Server will automatically add some underscores in between and alphanumeric's at end.

在表结构中，表名称显示类似“#TempTable__________________________________________________________________________________________________________0000000004CB”的内容。实际上，每个临时表名称的总长度将为 128 。为了以不同方式处理多个会话中的相同临时表名称，SQL Server 将自动在中间添加一些下划线，并在末尾添加字母数字。

METHOD 2 – Using SP_COLUMNS

方法 2 – 使用 SP_COLUMNS

EXEC TempDB..SP_COLUMNS '#TempTable';

METHOD 3 – Using System Tables like INFORMATION_SCHEMA.COLUMNS, SYS.COLUMNS, SYS.TABLES

方法 3 – 使用系统表，如 INFORMATION_SCHEMA.COLUMNS、SYS.COLUMNS、SYS.TABLES

SELECT * FROM TempDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN (
SELECT NAME FROM TempDB.SYS.TABLES WHERE OBJECT_ID=OBJECT_ID('TempDB.dbo.#TempTable')
);
GO

SELECT * FROM TempDB.SYS.COLUMNS WHERE OBJECT_ID=OBJECT_ID('TempDB.dbo.#TempTable');
GO

SELECT * FROM TempDB.SYS.TABLES WHERE OBJECT_ID=OBJECT_ID('TempDB.dbo.#TempTable');
GO