SQL 임시 테이블의 데이터 유형 찾기

SQL 임시 테이블의 데이터 유형 찾기

---

함수에서 사용할 수있는 사용 #temp 테이블 사용에서 @table 변수로 전환해야합니다.

내 쿼리는 다음과 같이 #temp (새로운 테이블에서)에 삽입을 사용합니다.

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

#temp 테이블과 동일한 데이터 열 및 데이터 유형으로 @table 변수를 만들 수 있습니다. #temp 테이블에서 열의 유형을 쉽게 사용할 수있는 방법이 있습니까?

---

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

이것은 varchar의 max_length를 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);

---

대답은 데이터 유형을 수락하지 않습니다. 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')

---

해시 테이블이 저장되는 곳이기 때문에 해시 테이블에 대한 세부 정보를 얻으려면 tempdb 데이터베이스에서 sp_help 프로세스를 한정해야합니다. 다른 데이터베이스에서 sp_help를 수행하면 해당 데이터베이스에서 오류가 발생합니다.

쿼리가 tempdb 외부에서 실행되는 경우 다음을 사용할 수 있습니다.

exec tempdb..sp_help #temp

이 절차의 한 가지 이점은 열 데이터 유형에 대한 텍스트 설명이 포함되어있는 것입니다. 이렇게하면 테이블 변수를 생성하기 위해 임시 테이블의 정의를 사용하려는 경우와 같이 다른 쿼리에 복사하여 넣습니다.

Syscolumns 테이블에서 동일한 정보를 사용할 수 있도록 직접 매핑하는 유형에 대한 숫자 식별자를 제공합니다. sp_help를 사용하면 단계를 절약 할 수 있습니다.

---

수행하려는 작업은 쿼리하는 열의 시스템 유형에 대한 정보를 얻는 것입니다.

들어있는 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);

---

다른 답변은 필요한 정보를 제공하지만 테이블 변수를 정의 할 때 모든 정보를 입력해야합니다.

다음 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');

---

게으른 길로 가고

use tempdb
GO
EXECUTE sp_help #temp

참고 URL : https://stackoverflow.com/questions/7486941/finding-the-data-types-of-a-sql-temporary-table