查找 SQL 临时表的数据类型

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

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

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

有没有一种简单的方法可以找出 # temp 表中列的数据类型，这样我就可以用与 # temp 相同的列和数据类型创建@table 变量？

116925 次浏览
小开

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

小开

我会选择懒散的路线

use tempdb
GO
EXECUTE sp_help #temp
小开
最佳答案

您需要确保 sp_help在表所在的同一个数据库中运行(temdb)。您可以通过直接在调用前面加上前缀来实现这一点:

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

或者在 tempdb.sys.columns前面加一个 join:

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，但是这是一个很好的开始。

在 SQL Server 2012或更好的版本中，您可以使用一个新的 DMF 来描述结果集，这样就消除了这个问题(并且还可以为您组装 max _ length/Precision/scale)。但是它不支持 # temp 表，所以只需注入查询 没有入口:

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);
小开

您需要限定 sp _ help 进程才能从 temdb 数据库运行，以获得有关哈希表的详细信息，因为哈希表实际上就存储在这个数据库中。如果尝试从另一个数据库运行 sp _ help，则会得到一个错误，即该数据库中不存在该表。

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

exec tempdb..sp_help #temp

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

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

小开

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

对于 SQL Server 2012 and later，可以使用 Dm _ exec _ description _ first _ result _ set函数。它返回关于列的非常详细的信息，system_type_column保存完整的系统类型定义(可以在表定义中使用) :

例如:

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

enter image description here

小开

接受的答案不提供数据类型。将 temdb.sys.column 与 sys.type 连接起来，就会得到答案的注释中提到的数据类型。但是加入 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')
小开

其他答案将提供您需要的信息，但是仍然需要您在定义表变量时将其全部键入。

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

这可以为您节省大量时间，而不必手动键入表定义，比如:

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

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

产出:

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

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

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

或者 只获取要使用此

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

查找 SQL 临时表的数据类型

方法1-使用 SP _ HELP

EXEC TempDB..SP_HELP #TempTable;

enter image description here

注 -

在表结构中，表名显示类似“ # TempTable _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _。实际上，每个临时表名称的总长度将是128。若要以不同方式处理多个会话中的同一临时表名称，SQLServer 将自动在两个会话之间和结尾处添加一些下划线。

方法2-使用 SP _ COLUMNS

EXEC TempDB..SP_COLUMNS '#TempTable';

enter image description here

方法3-使用诸如 INFORMATION _ SCHEMA. Colummns，SYS.Colummns，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

enter image description here


提交答案