[MSSQL] 데이터베이스 확장 속성 조회 및 추가(sp_msforeachdb)

데이터베이스 목록이 많이 증가하면서 각각의 디비의 담당자와 업무가 무엇인지 구분하기가 힘들졌습니다.
그래서 오늘은 디비의 확장 속성을 이용해서 해당 DB의 담당자와 업무설명 추가하고 조회하는 방법에 대해서 알아보려고 합니다. 추가로 테이블,컬럼 확장속성 추가 및 조회하는 방법도 알아보겠습니다.

 

DB 확장 속성

DB 확장 속성 추가

데이터베이스의 확장 속성을 추가하는 방법은 2가지가 있습니다.

 

 

 

 

 

첫번째는 GUI를 통해서 하는방법입니다.
1. 데이터베이스를 마우스로 우클릭 하고 "속성"을 클릭합니다.
2. 속성에 왼쪽 페이지 선택 부분의 리스트에서 "확장 속성"을 클릭합니다.
3. 왼쪽에 이름과 값을 입력할 수 있는 페이지가 활성화 됩니다.
4. 이름에는 담당자, 값에는 업무설명을 추가하고 확인을 눌러 저장을 합니다.

두번째는 SQL 쿼리문을 이용한 방법입니다.

문법: EXEC [DB명].sys.sp_addextendedproperty @name=N'이름', @value=N'값' 

USE [master]
GO
EXEC [test].sys.sp_addextendedproperty @name=N'급여', @value=N'급여 관리' 

 

DB 확장 속성 조회

데이터베이스에 확장 속성을 추가하였으니 추가한 내용을 조회해 보도록 하겠습니다.
(디비 하나하나 확인 하려면 위의 DB 확장 속성 추가에있는 첫번째 방법처럼 들어가서 보시면 됩니다.)

 

 

 

 

 

1. 데이터를 담을 임시 테이블을 생성합니다.

CREATE TABLE #DB_EP (
DatabaseName varchar(255), 
PropertyName varchar(max), 
PropertyValue varchar(max)
)

2. sp_msforeachdb 문을 사용해서 생성한 임시테이블에 값을 넣어주도록 합니다.
시스템 프로시저 sp_msforeachdb 모르는 분들이 있을수 있으니 잠시 설명을 하도록 하겠습니다.
sp_msforeachdb 프로시저는 전체 데이터베이스에 어떤 작업을 하는 일이 생길경우 사용하기위해서
MS에서 제공하는 시스템 프로시저 입니다.(전체 테이블에 대한 프로시저는 sp_MSforeachtable 입니다.

여기서 아래에 쿼리에 쓴 ? 를 각 데이터베이스나 테이블 이름으로 인식을 합니다.
Foreach와 같이 테이블 처음부터 끝까지 루프하게 되는데 아래의 쿼리문을 실행해 보시면 확인이 가능합니다.

- EXEC sp_MSforeachdb 'PRINT '' ? '' '
- EXEC sp_MSforeachtable 'PRINT '' ? '' '
- EXEC sp_msforeachdb 'sp_helpfile'

테스트도 끝났으니 이제 만들어둔 임시 테이블에 데이터를 넣어보도록 하겠습니다.

EXEC sp_msforeachdb 'INSERT INTO #DB_EP SELECT ''?'' , 
            CAST(name AS varchar), CAST(Value AS varchar) 
        FROM [?].sys.extended_properties WHERE class=0'

위의 구문이 이해가 안된다면 아래의 구문이 생성된 데이터베이스 수량만큼 돌아간다고 생각하면 되겠습니다.

문법 : [디비명].SYS.EXTENDED_PROPERTIES
SELECT * FROM TEST.SYS.EXTENDED_PROPERTIES

3. 데이터가 들어간 임시 테이블 조회
확장 속성이 있는 데이터베이스만 조회가 됩니다.

SELECT * FROM #DB_EP

만약 확장 속성이 없는 Null의 데이터베이스 까지 조회하려면 아래와 같이 입력하면 볼 수 있습니다.

SELECT db.Name, #DB_EP.PropertyName, #DB_EP.PropertyValue
FROM sys.databases db
LEFT OUTER JOIN #DB_EP
    ON db.name = #DB_EP.DatabaseName

 

테이블 확장 속성

테이블에 확장속성을 추가, 삭제, 조회하는 방법에 대해서 알아보겠습니다.

 

 

테이블 확장속성 추가

문법 : EXEC sys.sp_addextendedproperty @name=N'이름', @value=N'값' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'테이블명'
실행 : EXEC sys.sp_addextendedproperty @name=N'부서테이블', @value=N'부서정보관리' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'부서'

 

테이블 확장속성 삭제

문법 : EXEC [test].sys.sp_dropextendedproperty @name=N'이름' 
실행 : EXEC [test].sys.sp_dropextendedproperty @name=N'급여' 

 

테이블 확장속성 조회 

SELECT 
NAME , 
(SELECT TOP 1 VALUE FROM SYS.EXTENDED_PROPERTIES WHERE MAJOR_ID = A.ID AND MINOR_ID = 0 ) COMMENT , 
crdate ,*
FROM SYSOBJECTS A WHERE RTRIM(A.XTYPE) = 'U' AND NAME = '테이블명'--부서

 

컬럼에 확장 속성

컬럼에 확장속성을 추가, 삭제, 조회하는 방법에 대해서 알아보겠습니다.

 

 

컬럼 확장속성 추가

문법 : EXEC sys.sp_addextendedproperty @name=N'이름', @value=N'값' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'테이블명', @level2type=N'COLUMN',@level2name=N'컬럼명'
실행 : EXEC sys.sp_addextendedproperty @name=N'부서ID', @value=N'부서아이디 값입니다.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'부서', @level2type=N'COLUMN',@level2name=N'dept_id'

컬럼 확장속성 삭제

문법 : EXEC sys.sp_dropextendedproperty @name=N'이름' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'테이블명', @level2type=N'COLUMN',@level2name=N'컬럼명'
실행 : EXEC sys.sp_dropextendedproperty @name=N'부서ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'부서', @level2type=N'COLUMN',@level2name=N'dept_id'

 

컬럼 확장속성 조회 

SELECT 
NAME , 
(SELECT TOP 1 VALUE FROM SYS.EXTENDED_PROPERTIES WHERE MAJOR_ID = A.ID AND MINOR_ID = 0 ) COMMENT , 
OBJECT_NAME(ID) TABLENAME 
FROM SYSCOLUMNS A WHERE NAME = '컬럼명' --dept_id