Mancheol's SQL Blog :: Denali - Metadata enhancements

Denali - Metadata enhancements

SQL Server 2011. 9. 3. 17:28

SQL Denali에서는 메타데이터를 이용할 수 있는 DMV 및 구문들이 추가 되었습니다. 이전 버전에서는 이런 기능들을 이용하기 위해 system view를 조회 하거나 임시 테이블에 한번 넣은 뒤에 메타데이터를 조작/확인해야 했습니다. 하지만 SQL Denali에서는 이런 추가적인 작업 없이 손쉽게 메타데이터를 조회 하거나 사용할 수 있습니다.

테스트를 위해 다음과 같이 테이블 및 저장프로시저를 생성하도록 하겠습니다.

CREATE TABLE t1 (

col1 int

, col2 decimal(12,2)

, col3 varchar(max)

)

CREATE PROC usp_t1

SELECT * FROM t1
GO

1. 쿼리 결과에 대한 메타데이터 사용 (sys.dm_exec_describe_first_result_set)
이전 버전에서는 메타데이터 정보를 조회하기 위해 쿼리나 저장프로시저의 결과를 임시테이블에 저장한 다음 해당 임시테이블의 정보를 system view를 이용하여 조회해야만 했습니다. 하지만 SQL Denali에서 제공하는 새로운 DMV를 이용하면 쿼리나 저장프로시저의 결과에 대한 메타데이터 정보를 좀 더 손쉽게 조회할 수 있습니다.

이전 버전에서는 t1 테이블에 대한 메타데이터를 조회하기 위해서는 다음과 같은 쿼리를 이용할 수 있었습니다.

SELECT c.name

, t.name

, c.max_length

, c.[precision]

, c.scale

, c.is_nullable

FROM sys.columns AS c

INNER JOIN sys.types AS t

ON c.system_type_id = t.system_type_id

AND c.user_type_id = t.user_type_id

WHERE c.[object_id] = OBJECT_ID('dbo.t1')

ORDER BY c.column_id;

SQL Denali에서는 sys.dm_exec_describe_first_result_set DMV에 파라메터로 쿼리나 저장프로시저의 실행 구문을 넘겨주기만 하면 손쉽게 메타데이터를 조회할 수 있습니다.

SELECT

name

, system_type_name

, is_nullable

FROM sys.dm_exec_describe_first_result_set

(N'SELECT * FROM dbo.t1;', NULL, 0 ) AS f

ORDER BY column_ordinal;

이전 버전에서 사용하던 system view를 이용한 방식과 새로운 DMV의 결과가 비슷해 보이지만 새로운 DMV의 경우 join 결과에 대한 메타데이터를 생성 한다거나 저장프로시저 결과에 대한 메타데이터를 가져올 수 있다는 점에서 기존 방식보다 훨씬 더 유연하게 사용할 수 있습니다.
또, 결과 중 system_type_name 부분을 보면 완전한 형태의 자료형을 보여주고 있는데 이런 부분은 CREATE TABLE 스크립트를 생성할 때 좀 더 간편함을 제공해 주게 되며, 순수하게 CREATE TABLE스크립트 생성을 위해서는 비슷한 기능을 하는 sys.dm_exec_describe_first_result_set_for_object DMV를 이용할 수도 있습니다.
필요에 따라 저장프로시저 형태의 sp_describe_first_result_set 명령을 사용할 수도 있는데, Application에서 특정 테이블이나 저장프로시저의 메타데이터를 조회할 때 유용할 것 같습니다.

2. 정의되지 않은 파라메터가 포함된 쿼리의 메타데이터 예측 (sys.sp_describe_undeclared_parameters)

만일 다음과 같은 쿼리를 수행하기 위해서는 파라메터를 선언한 다음 값을 입력해 주어야 합니다. 이 쿼리의 경우 단순한 쿼리이지만 쿼리를 작성하다 보면 파라메터가 10개 이상 선언되는 경우는 흔하게 볼 수 있습니다. 또는 여러 테이블을 join한 쿼리일 경우 자료형을 맞춰가며 파라메터를 선언하는 작업은 불필요하게 많은 시간을 뺏을 수 있습니다.

SELECT * FROM sys.objects

WHERE [object_id] = @ObjectID
OR (name LIKE @ObjectName);

SQL Denali에서는 다음과 같은 저장프로시저를 이용하여 파라메터의 자료형을 예측할 수 있습니다. 이 결과값을 이용하여 선언부의 샘플을 좀 더 손쉽게 생성해 낼 수 있습니다.

EXEC sys.sp_describe_undeclared_parameters

@tsql = N'SELECT * FROM sys.objects

WHERE [object_id] = @ObjectID

OR (name LIKE @ObjectName);';

하지만 파라메터를 예측하는 작업이기 때문에 모든 결과가 올바르게 나올 수 없습니다. 자세히 보신분은 눈치 채셨겠지만 위 예제의 sys.objects view에서 name 컬럼의 자료형은 sysname 즉, nvarchar(128)입니다. 이런 부분에 대해서는 필요한 경우 수정을 해서 사용해야 합니다.

다음은 몇가지 특별한 경우를 정리한 내용입니다.

== 오류 발생

SELECT * FROM t1 WHERE @p1 = @p2

SELECT * FROM t1 WHERE c1 = @p1 + @p2

SELECT * FROM t1 WHERE @p1 = SUBSTRING(@p2, 2, 3)

== c1 컬럼이 char(30)일 경우 >, <비교를 하게 되면 varchar(8000)을 반환하게 됨. (= 비교의 경우 정상적으로 반환)

SELECT * FROM t WHERE c1 > @p

3. 결과에서 데이터 타입 및 컬럼명 변경 (WITH RESULT SETS)
이전 버전에서 저장프로시저 수행시 컬럼명이나 데이터타입을 변경할 수 있는 방법은 없었습니다. 하지만 SQL Denali에서는 다음과 같이 WITH RESULT SETS 구문을 이용하여 결과값의 메타데이터를 재정의 할 수 있습니다.