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

 

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

CREATE TABLE t1 (

       col1 int

     col2 decimal(12,2)

,      col3 varchar(max)

)

 

CREATE PROC usp_t1

AS

       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 구문을 이용하여 결과값의 메타데이터를 재정의 있습니다.

exec sp_readerrorlog

 

exec sp_readerrorlog

WITH RESULT SETS (

       (LogDateTime datetime

       ,ProcessInfo varchar(50)

       ,LogText varchar(max)

       )

)

예를 들어 다음과 같은 시나리오들에서 유용하게 이용할 있을것 같습니다.

1.  2가지 시스템이 하나의 저장프로시저를 통해 로그 데이터를 조회하는 경우 한쪽에서는 날짜 정보를 char 타입으로 원하고, 다른 한쪽에서는 datetime으로 원하는 경우

2.  Text라는 컬럼의 이름을 명확히 하기 위해 LogText라는 컬럼 이름을 사용하고 싶은 경우

3.  interface용으로 특정 저장프로시저를 호출해주는 저장 프로시저를 만드는 경우

 
[참고]

SQL Server v.Next (Denali) : Metadata enhancements

http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/20/sql-server-v-next-denali-metadata-discovery.aspx

 

sys.dm_exec_describe_first_result_set (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ff878258(v=sql.110).aspx

 

sys.dm_exec_describe_first_result_set_for_object (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ff878236(v=SQL.110).aspx

 

sp_describe_first_result_set (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ff878602(v=SQL.110).aspx

 

sp_describe_undeclared_parameters (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ff878260(v=SQL.110).aspx

 

EXECUTE (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms188332(SQL.110).aspx

 

 

하만철 / Ha ManCheol

YOUR COMMENT IS THE CRITICAL SUCCESS FACTOR FOR THE QUALITY OF BLOG POST