[MsSQL] 테이블 정의서 만들기 - 쿼리로 추출하기

테이블 정의서 (Table Spec)

테이블의 자세한 내용에 대해 설명 할 때 꼭 필요한 것이 테이블 정의서이다.

 

테이블의 존재하는 컬럼과 컬럼들의 Data Type 등을 기록하는 문서이며,

해당 문서를 통해 DBA에게 Table 생성을 요청하거나, 프로젝트 명세를 위해 내역을 첨부하는 등 필요가 많다.

 

보통은 DBA에게 요청하는 문서로 작성되어 오지만, 기존의 테이블의 정의를 확인하고 문서화 하기위해 정의서를 생성해 내야할 필요가 있다.

 

그럴 때 사용하기 적절한 쿼리에 대해 소개해보도록 하겠다.

 

Query

Database에 생성되어있는 Table에 대한 테이블 정의서를 추출하는 쿼리는 아래와 같다.

select o.name as table_name,
       a.value as table_comment,
       b.name as column_name,
       c.data_type as column_type,
       isnull(cast(c.character_maximum_length as varchar(10)), 
              isnull(cast(c.numeric_precision as varchar(10)) 
              + ',' 
              + cast(c.numeric_scale as varchar(10)), '')
             ) as column_length,
       c.column_default,
       'PK('+ cast(e.ordinal_position as varchar(10)) + ')' as pk,
       case
         when c.is_nullable = 'YES' then 'Y'
         when c.is_nullable = 'NO' then 'N' 
        end as column_nullable,
       d.value as column_comment
  from SYSOBJECTS o
  left outer join SYS.EXTENDED_PROPERTIES a
    on o.id = a.major_id and a.minor_id = 0
  left outer join SYS.COLUMNS b
    on o.id = b.object_id
  left outer join INFORMATION_SCHEMA.COLUMNS c
    on o.name = c.table_name
   and b.name = c.column_name
  left outer join SYS.EXTENDED_PROPERTIES d
    on o.id = d.major_id
   and b.column_id = d.minor_id
  left outer join INFORMATION_SCHEMA.KEY_COLUMN_USAGE e
    on o.name = e.table_name
   and b.name = e.column_name
 where o.xtype = 'U'
   and o.name = [TABLE NAME]
 order by table_name,
          column_name

 

where절에 원하는 테이블명을 입력하여 테이블 정의서를 추출 할 수 있다.

 

Table_Name : 테이블명

Table_Comment : 테이블 주석

Column_name : 컬럼명

Column_Type : 컬럼 타입

Column_Length :컬럽 타입의 길이

Column_Default : 컬럼 기본 값

PK : Primary key ( 복합키의 순서까지 포함 )

Column_Nullable : Null 가능 유무

Column_Comment : 컬럼 주석

 

기본적으로 필요한 내용을 추출한다.