데이터베이스 개체 확장 속성 추가/조회/수정하기

728x90

테이블 생성할 때 컬럼 설명을 꼭 작성하는 편입니다. 개발할 때는 명확한 컬럼명이라고 생각하지만 시간이 지나면 정말 모르겠어서 시간이 좀 더 걸리더라도 나와 다른 누군가를 위해서 빼놓지 않고 작성하는 편입니다.

Description 생성 구문

sp_addextendedproperty  
    [ @name = ] { 'property_name' }  
    [ , [ @value = ] { 'value' }   
        [ , [ @level0type = ] { 'level0_object_type' }   
          , [ @level0name = ] { 'level0_object_name' }   
                [ , [ @level1type = ] { 'level1_object_type' }   
                  , [ @level1name = ] { 'level1_object_name' }   
                        [ , [ @level2type = ] { 'level2_object_type' }   
                          , [ @level2name = ] { 'level2_object_name' }   
                        ]   
                ]  
        ]   
    ]   
[;]

SQL Server에서는 시스템 저장 프로시저를 사용하여 많은 관리 및 정보 활동을 수행할 수 있습니다. 그중 sp_addextendedproperty는 데이터베이스 개체에 확장된 새 속성을 추가할 수 있는 시스템 저장 프로시저입니다.

@name

추가할 속성의 이름을 지정해줍니다.

예시) MS_Description, Caption, MS_DescriptionExample

생성된 속성은 SYS.EXTENDED_PROPERTIES 에서 조회할 수 있습니다.

-- 테이블 생성 생략

-- 테이블의 컬럼에 확장 속성 추가
EXEC sp_addextendedproperty   
@name = N'test',   
@value = '명언순번입니다.',  
@level0type = N'Schema', @level0name = 'dbo',  
@level1type = N'Table',  @level1name = 'Background_LifeQuote',  
@level2type = N'Column', @level2name = 'QuoteId';  

-- 추가된 확장 속성 조회
SELECT * FROM SYS.EXTENDED_PROPERTIES

Caption과 MS_Description 차이
공식 문서에서는 'Caption'과 ' MS_DescriptionExample '으로 만들어서 @name을 사용자 마음대로 만들 수 있는 것을 알게 되었습니다. 그런데, MS_Description을 주로 사용하는 이유는, SSMS의 GUI 디자인 창에서 설명 칸에 연결되는 name이 MS_Description입니다.

@value

속성과 연결할 값으로 기본값은 NULL이며 크기는 7,500 byte를 초과할 수 없습니다.

예시) 명언순번입니다.(자동증가값)

@level0type

레벨 0 개체의 유형을 지정해줍니다.

예시) ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE SERVICE BINDING, ROUTE, SCHEMA, SERVICE, USER, TRIGGER, TYPE, PLAN GUIDE

@level0name

레벨 0 개체의 이름입니다.

@level1type

레벨 1 개체의 유형을 지정해줍니다.

예시) TABLE, TYPE, ...

@level1name

레벨 1 개체의 이름입니다.

(개체가 존재해야 합니다.)

@level2type

레벨 2의 유형을 지정해 줍니다.

예시) COLUMN

@level2name

레벨 2 개체의 이름입니다.

확장 속성 추가 기능 활용

데이터베이스
테이블의 컬럼
파일 그룹
스키마
테이블
역할
타입
사용

[역할]

USE AdventureWorks2022;   
GO  
CREATE APPLICATION ROLE Buyers  
WITH Password = '987G^bv876sPY)Y5m23';   
GO  
EXEC sys.sp_addextendedproperty   
@name = N'MS_Description',   
@value = N'Application Role for the Purchasing Department.',  
@level0type = N'USER',  
@level0name = 'Buyers';

[테이블]

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'명언' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Background_LifeQuote'

테이블의 컬럼에 확장 속성 추가하기

SQL 구문으로 생성하기

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'명언순번(Auto-Increment)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Background_LifeQuote', @level2type=N'COLUMN',@level2name=N'QuoteId';

SSMS GUI 디자인 사용하기

설명을 추가하려는 테이블을 우클릭하여 [디자인]을 선택합니다.

컬럼을 선택하고, [설명]에 내용을 작성한 후 저장합니다.

추가한 확장 속성(MS_Description) 조회하기

SSMS 디자인에서 조회하기

테이블 확장 속성 조회하기

설명을 조회하고 싶은 테이블의 속성 창을 열어 [확장 속성] 페이지를 확인합니다.

테이블 컬럼 확장 속성 조회하기

확장 속성을 조회하고 싶은 테이블의 디자인 창을 열어 컬럼의 [설명]을 확인합니다.

쿼리를 이용하여 조회하기

디자인 모드는 한 개씩 확인해야 하는데 쿼리를 이용하면 원하는 대로 구성해서 조회할 수 있습니다.

저는 테이블 이름으로, 속한 데이터베이스 이름, 테이블 이름과 설명, 컬럼의 정렬순서, 이름, 설명, 데이터 타입 정보를 조회하는 쿼리를 사용합니다.

DECLARE @TABLE NVARCHAR(50) = 'Background_LifeQuote';


SELECT F.TABLE_CATALOG				as DatabaseName 
	 , A.NAME						as TableName
     , C.VALUE						as TableDesc
	 , D.COLORDER					as ColumnIndex
     , D.NAME						as ColumnName
     , E.VALUE						as ColumnDescription
     , F.DATA_TYPE					as ColumnType
     , F.CHARACTER_OCTET_LENGTH		as ColumnLength
     , F.IS_NULLABLE	            as ColumnIsNull
  FROM SYSOBJECTS A WITH (NOLOCK)
 INNER JOIN SYSUSERS B WITH (NOLOCK)        ON A.UID = B.UID
 INNER JOIN SYSCOLUMNS D WITH (NOLOCK)        ON D.ID = A.ID
 INNER JOIN INFORMATION_SCHEMA.COLUMNS F WITH (NOLOCK)
    ON A.NAME = F.TABLE_NAME
   AND D.NAME = F.COLUMN_NAME
  LEFT JOIN SYS.EXTENDED_PROPERTIES C WITH (NOLOCK)
    ON C.MAJOR_ID = A.ID
   AND C.MINOR_ID = 0
   AND C.NAME = 'MS_Description'
  LEFT JOIN SYS.EXTENDED_PROPERTIES E WITH (NOLOCK)
    ON E.MAJOR_ID = D.ID
   AND E.MINOR_ID = D.COLID
   AND E.NAME = 'MS_Description'  
 WHERE A.TYPE = 'U'
   AND A.NAME = @TABLE
 ORDER BY D.COLORDER

추가한 속성 수정하기

수정은 간단합니다. 수정에 대한 시스템 저장 프로시저 sp_updateextendedproperty 를 사용합니다.

EXEC sys.SP_UPDATEEXTENDEDPROPERTY @name=N'MS_Description', @value=N'내용수정하기하기하기' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Background_LifeQuote', @level2type=N'COLUMN',@level2name=N'QuoteId'

사용자 저장 프로시저로 생성하고 단축키로 등록해서 사용하고 싶어지네요.

참고 자료
MS
https://learn.microsoft.com/ko-kr/sql/relational-databases/system-stored-procedures/sp-addextendedproperty-transact-sql?view=sql-server-ver16
https://learn.microsoft.com/ko-kr/sql/relational-databases/system-stored-procedures/database-engine-stored-procedures-transact-sql?view=sql-server-ver16
https://learn.microsoft.com/ko-kr/sql/relational-databases/system-stored-procedures/system-stored-procedures-transact-sql?view=sql-server-ver16
https://learn.microsoft.com/ko-kr/sql/relational-databases/system-stored-procedures/sp-updateextendedproperty-transact-sql?view=sql-server-ver16
MS QnA
https://social.msdn.microsoft.com/Forums/sqlserver/ko-KR/820154d0-094c-43c5-a3be-9159bf95134e/exec-spaddextendedproperty-namencaption-namenmsdescription-?forum=sqlserversko