Recherche des types de données d'une table temporaire SQL

Question

Je dois passer d'une table #temp à une variable @table pour pouvoir l'utiliser dans une fonction.

Ma requête utilise insert dans #temp (à partir de plusieurs tables) comme ceci :

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

Existe-t-il un moyen simple de connaître les types de données des colonnes de la table #temp afin de pouvoir créer la variable @table avec les mêmes colonnes et types de données que #temp ?

Answer 1

La réponse acceptée ne donne pas le type de données. Rejoindre tempdb.sys.columns avec sys.types donne le type de données mentionné dans le commentaire de la réponse. Mais la jointure sur system_type_id donne une ligne supplémentaire avec le type de données "sysname". Au lieu de cela, "user_type_id" donne la solution exacte comme indiqué ci-dessous.

 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')

Answer 2

Les autres réponses vous donneront les informations dont vous avez besoin, mais vous demanderont toujours de tout saisir lorsque vous définissez la variable de table.

Le TSQL suivant vous permettra de générer rapidement la définition de la variable de table pour n'importe quelle table donnée.

Cela peut vous faire gagner beaucoup de temps au lieu de saisir manuellement des définitions de table comme :

 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

Sortir:

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

Answer 3

pour obtenir le nom des colonnes avec le type de données, utilisez ceci

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

ou Pour obtenir uniquement le nom des colonnes à utiliser

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