[SQL Server] SQL Server 테이블 컬럼에 설명 추가, 수정, 삭제 및 조회하기

테이블 컬럼에 설명 추가, 수정, 삭제 및 조회하기

SQL Server에는 테이블 생성 시 직접 코멘트를 추가하는 기능은 제공되지 않지만
확장속성을 활용하여 테이블 메타데이터와 비슷한 정보를 추가할 수 있습니다.

 

물론 DBeaver 등 IDE를 활용하면 더욱 간편하게 테이블을 생성/수정할 수 있지만

개발자라면 툴 뿐 아니라 코드를 다룰 수 있어야 하므로 배워보도록 하겠습니다.😤

 

예시를 위해 테이블 이름은 DUMMY_TABLE, 데이터베이스 이름은 임의로 DATA_VASE라고 하겠습니다.

테이블 생성

먼저 아래 쿼리로 Dummy 테이블을 생성하겠습니다.

CREATE TABLE DATA_VASE.DBO.DUMMY_TABLE (
    NAME   NVARCHAR(50) NOT NULL
  , AGE    NUMERIC(3)
  , RGS_DT DATETIME
)

 

확장속성으로 설명 추가하기

아래 쿼리를 이용해 테이블 컬럼마다 적절한 설명을 추가할 수 있습니다.

EXEC DATA_VASE.sys.sp_addextendedproperty 'MS_Description', N'이름', 'schema', N'dbo', 'table', N'DUMMY_TABLE', 'column', N'NAME';
EXEC DATA_VASE.sys.sp_addextendedproperty 'MS_Description', N'나이', 'schema', N'dbo', 'table', N'DUMMY_TABLE', 'column', N'AGE';
EXEC DATA_VASE.sys.sp_addextendedproperty 'MS_Description', N'등록일', 'schema', N'dbo', 'table', N'DUMMY_TABLE', 'column', N'RGS_DT';

 

설명 수정하기

설명을 잘못 추가했거나 수정이 필요할 때는 아래 쿼리를 이용해 수정할 수 있습니다.
sp_addextendedproperty라는 명령어가 sp_updateextendedproperty로 변경되었습니다.

EXEC DATA_VASE.sys.sp_updateextendedproperty 'MS_Description', N'이름_수정', 'schema', N'dbo', 'table', N'DUMMY_TABLE', 'column', N'NAME';
EXEC DATA_VASE.sys.sp_updateextendedproperty 'MS_Description', N'나이_수정', 'schema', N'dbo', 'table', N'DUMMY_TABLE', 'column', N'AGE';
EXEC DATA_VASE.sys.sp_updateextendedproperty 'MS_Description', N'등록일_수정', 'schema', N'dbo', 'table', N'DUMMY_TABLE', 'column', N'RGS_DT';

 

설명 삭제하기

센스있는 분들은 예상하실 수 있으셨겠지만, 설명을 삭제하고 싶은 경우 sp_dropextendedproperty로 바꾸기만 하면 되겠죠?
예 안됩니다.
drop시에는 설명을 입력할 필요가 없어서 설명에 해당하는 매개변수 하나를 빼주어야 정상적으로 실행됩니다.😁

EXEC DATA_VASE.sys.sp_dropextendedproperty 'MS_Description','schema', N'dbo', 'table', N'DUMMY_TABLE', 'column', N'NAME';
EXEC DATA_VASE.sys.sp_dropextendedproperty 'MS_Description','schema', N'dbo', 'table', N'DUMMY_TABLE', 'column', N'AGE';
EXEC DATA_VASE.sys.sp_dropextendedproperty 'MS_Description','schema', N'dbo', 'table', N'DUMMY_TABLE', 'column', N'RGS_DT';

 

설명 조회하기

테이블에 컬럼 설명을 추가한 뒤 아래 쿼리를 이용해 조회할 수도 있습니다.

SELECT 
    obj.name AS TableName,
    col.name AS ColumnName,
    ep.name AS PropertyName,
    ep.value AS PropertyValue
FROM 
    sys.extended_properties AS ep
INNER JOIN 
    sys.tables AS obj 
        ON ep.major_id = obj.object_id
LEFT JOIN 
    sys.columns AS col 
        ON ep.major_id = col.object_id 
       AND ep.minor_id = col.column_id
WHERE 1=1
    AND obj.name = 'DUMMY_TABLE'

 

• 설명 추가 : EXEC DB_NAME.sys.sp_addextendedproperty 'MS_Description', N'DESC', 'schema', N'dbo', 'table', N'TABLE_NAME', 'column', N'COLUMN_NAME';
• 설명 수정 : EXEC DB_NAME.sys.sp_updateextendedproperty 'MS_Description', N'DESC', 'schema', N'dbo', 'table', N'TABLE_NAME', 'column', N'COLUMN_NAME';
• 설명 삭제 : EXEC DB_NAME.sys.sp_dropextendedproperty 'MS_Description', 'schema', N'dbo', 'table', N'TABLE_NAME', 'column', N'COLUMN_NAME';

 

도움이 되어드렸다면 구독 / 좋아요 부탁드립니다.👏🏻

감사합니다.🙂