Поиск типов данных временной таблицы SQL

Мне нужно переключиться с таблицы #temp на переменную @table, чтобы я мог использовать ее в функции.

Мой запрос использует insert в #temp (из нескольких таблиц):

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

Есть ли простой способ узнать типы данных столбцов в таблице #temp, чтобы я мог создать переменную @table с теми же столбцами и типами данных, что и #temp?

Ответ 1

Вы должны убедиться, что sp_help работает в той же базе данных, где находится таблица (tempdb). Вы можете сделать это, добавив префикс вызова напрямую:

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

Или добавив префикс к 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');

Это не поможет вам, например, отрегулируйте max_length для varchar иначе, чем nvarchar, но это хорошее начало.

В 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);

Ответ 2

Принятый ответ не дает тип данных. Объяснение 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')

Ответ 3

вам нужно квалифицировать процесс sp_help для запуска из базы данных tempdb, чтобы получить подробную информацию о хеш-таблице, потому что там, где хеш-таблица фактически хранится. Если вы попытаетесь запустить sp_help из другой базы данных, вы получите сообщение об ошибке, что таблица не существует в этой базе данных.

Если ваш запрос выполняется вне tempdb, как я предполагаю, вы можете запустить следующее:

exec tempdb..sp_help #temp

Одно из преимуществ этой процедуры заключается в том, что она содержит текстовое описание типов данных столбцов для вас. Это упрощает копирование и вставку в другой запрос, например. если вы пытаетесь использовать определение таблицы temp для создания переменной таблицы.

Вы можете найти ту же информацию в таблице Syscolumns, но она даст вам числовые отступы для типов, которые вам придется отображать сами. Использование sp_help позволит вам сделать шаг.

Ответ 4

То, что вы пытаетесь сделать, - это получить информацию о системных типах столбцов, которые вы запрашиваете.

Для SQL Server 2012 and later вы можете использовать sys.dm_exec_describe_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);

Ответ 5

Другие ответы дадут вам необходимую информацию, но все же потребуют, чтобы вы все это напечатали, когда определяете переменную таблицы.

Следующий 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)

Ответ 6

Да, типом данных таблицы temp будут типы данных столбцов, которые вы выбираете и вставляете в него. Поэтому просто посмотрите на инструкцию select и определите каждый тип данных на основе выбранного столбца.

Ответ 7

чтобы получить имя столбца с типом данных, используйте этот

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

или Чтобы получить только имя столбца для использования этого

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

Ответ 8

Я бы пошел по ленивому маршруту и использовал

use tempdb
GO
EXECUTE sp_help #temp