[2k8] 개체 COMMENT 저장 시스템 테이블

/********************************************************************************************
-- Title : [2k8] 개체 COMMENT 저장 시스템 테이블
-- Reference : wispcorp.com 정명훈K
-- Key word : comment 개체 설명 sys.extended_properties sp_addextendedproperty, description
                    fn_listextendedproperty sp_dropextendedproperty sp_updatedextendedproperty
********************************************************************************************/

---------------------------------------------------------
-- 조회
---------------------------------------------------------
SELECT *, object_name(major_id)
FROM sys.extended_properties;

---------------------------------------------------------
-- 데이터베이스
---------------------------------------------------------
-- DB 등록
EXEC sp_addextendedproperty
     @name = 'DB_Description'
   , @value = 'TESTDB 데이터베이스';

-- 확인
SELECT *
FROM fn_listextendedproperty('DB_Description', default, default, default, default, default, default);

---------------------------------------------------------
-- 테이블
---------------------------------------------------------
-- 정의
EXEC sp_addextendedproperty
     @name = 'DB_Description'
   , @value = 'Tbl_1 테이블'
   , @level0type = 'SCHEMA'
   , @level0name = 'dbo'
   , @level1type = 'TABLE'
   , @level1name = 'tbl_1';

-- 확인
SELECT *
FROM fn_listextendedproperty('DB_Description', 'SCHEMA', 'dbo', 'TABLE', 'tbl_1', default, default);

---------------------------------------------------------
-- 컬럼
---------------------------------------------------------
-- 정의
EXEC sp_addextendedproperty
     @name = 'DB_Description'
   , @value = 'col1 컬럼'
   , @level0type = 'SCHEMA'
   , @level0name = 'dbo'
   , @level1type = 'TABLE'
   , @level1name = 'tbl_1'
   , @level2type = 'COLUMN'
   , @level2name = 'col1';

EXEC sp_addextendedproperty
     @name = 'DB_Description'
   , @value = 'col2 컬럼'
   , @level0type = 'SCHEMA'
   , @level0name = 'dbo'
   , @level1type = 'TABLE'
   , @level1name = 'tbl_1'
   , @level2type = 'COLUMN'
   , @level2name = 'col2';

EXEC sp_addextendedproperty
     @name = 'DB_Description'
   , @value = 'col4 컬럼'
   , @level0type = 'SCHEMA'
   , @level0name = 'dbo'
   , @level1type = 'TABLE'
   , @level1name = 'tbl_1'
   , @level2type = 'COLUMN'
   , @level2name = 'col4';

-- 전체 컬럼 확인
SELECT *
FROM fn_listextendedproperty('DB_Description', 'SCHEMA', 'dbo', 'TABLE', 'tbl_1', 'column', default);

---------------------------------------------------------
-- 코드(사용자정의)
---------------------------------------------------------
-- 정의
EXEC sp_addextendedproperty
     @name = 'COL_Code'
   , @value = 'CD00001'
   , @level0type = 'SCHEMA'
   , @level0name = 'dbo'
   , @level1type = 'TABLE'
   , @level1name = 'tbl_1'
   , @level2type = 'COLUMN'
   , @level2name = 'col2';

-- 전체 컬럼 확인
SELECT *
FROM fn_listextendedproperty('COL_Code', 'SCHEMA', 'dbo', 'TABLE', 'tbl_1', 'column', 'col2');

---------------------------------------------------------
-- COMMENT 삭제, 업데이트
---------------------------------------------------------
EXEC sp_dropextendedproperty @name = N'DB_Description';
EXEC sp_updateextendedproperty
     @name = 'COL_Code'
   , @value = 'CD99999' /*변경된 값*/
   , @level0type = 'SCHEMA'
   , @level0name = 'dbo'
   , @level1type = 'TABLE'
   , @level1name = 'tbl_1'
   , @level2type = 'COLUMN'
   , @level2name = 'col2';