땅꼬마의 MSSQL 테이블 디스크립션과 활용!

업무도중 컬럼명을 보면 어떤 값인지 잘 이해되지 않는 부분이 있다.

테이블 생성한 후 디스크립션을 남겨 놓으면 후에 조금 더 편하게 이해할 수 있어 테이블생성 후 디스크립션을 생성하고, SP를 생성하여 어떻게 활용하는지 알아보자

 

시스템SP인 sp_addextendedproperty 를 사용하고자 한다.

 

간단하게 USER 테이블을 생성해보자

 

CREATE TABLE USER_M (
	USER_ID VARCHAR(20) PRIMARY KEY
    , USER_PW VARCHAR(30) NOT NULL
    , USER_NAME VARCHAR(10) NOT NULL
    , USER_EMAIL VARCHAR(30)
    , USER_TEL VARCHAR(13)
    , VIP_USER_YN CHAR(1) DEFAULT 'N'
)

USER_ID를 PK로 하는 유저 테이블을 생성했다.

 

MSSQL매니지먼트에서 ALT + F1 키를 누르면 테이블의 요약정보(테이블생성일,컬럼명과 타입, 인덱스, pk 등)를 얻을 수 있다. 컬럼을 보면 아래와 같은 이미지로 확인이 된다.

<예시1> 이전에 만들어놓은 EMP 테이블

 

하지만 컬럼명만으로 저 컬럼이 무엇을 의미하는지 명확하지 않다. 그래서 아래와 같이 디스크립션을 남겨보자!

EXEC sp_addextendedproperty @name=N'MS_Discription', @value=N'남길디스크립션', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'해당테이블',@level2type=N'COLUMN', @level2name=N'해당컬럼'
--예시
EXEC sp_addextendedproperty @name=N'MS_Discription', @value=N'사용자아이디', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'USER_M',@level2type=N'COLUMN', @level2name=N'USER_ID'
EXEC sp_addextendedproperty @name=N'MS_Discription', @value=N'사용자패스워드', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'USER_M',@level2type=N'COLUMN', @level2name=N'USER_PW'
EXEC sp_addextendedproperty @name=N'MS_Discription', @value=N'VIP사용자여부', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'USER_M',@level2type=N'COLUMN', @level2name=N'VIP_USER_YN'

 

그럼 생성된 디스크립션을 어떻게 조회하면 될까?

SP를 생성하면 쉽게 조회할 수 있다.

 

CREATE PROCEDURE SP_GET_TABLEDESCRIPTION
	@I_TABEL_NAME VARCHAR(200)
    
AS
BEGIN
	SELECT 
    	A.TABLE_NAME --테이블명
        ,C.VALUE AS TABLE_DESCRIPTION --테이블주석
        ,A.COLUMN_NAME --컬럼명
        ,A.DATA_TYPE --컬럼의 데이터 타입
        ,ISNULL(CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR), CAST(A.NUMERIC_PRECISION AS VARCHAR) + ',' + CAST(A.NUMERIC_SCALE AS VARCAHR)) AS COLUMN_LENGTH --컬럼길이
        ,A.COLUMN_DEFAULT --기본설정값
        ,CASE WHEN A.IS_NULLABLE = 'YES' THEN 'Y' ELSE 'N' END AS IS_NULLABLE --필수값여부
        ,B.VALUE AS COLUMN_DESCRIPTION --컬럼주석
    FROM INFORMATION_SCHEMA.ColumnS A
    LEFT JOIN SYS.EXTENDED_PROPERTIES B
    ON A.OBJECT_ID(A.TABLE_NAME) = B=Major_id
    AND A.Ordinal_position = B=Minor_id
    LEFT JOIN (
            SELECT OBJECT_ID(Objname) TABLE_ID, value
            FROM ::fn_listextendedproperty(NULL,'user','dbo','Table',null,null,null)
    		  )C
	ON C.TABLE_ID = A.OBJECT_ID(A.TABLE_NAME)
    WHERE A.TABLE_NAME = @I_TABEL_NAME
    ORDER BY A.TABLE_NAME, A.Ordinal_position
 END

*오타가 있을 수 있다

 

해당 프로시저를 생성하였고 실행시키면 아래와 같은 결과를 얻을 수 있다.

EXEC SP_GET_TABLEDESCRIPTION USER_M

 

<디스크립션 결과>

 

디스크립션을 활용해서 해당 컬럼에 주석을 남겨 관리를 잘 해보자!!