테이블 및 컬럼 설명 추가/수정/조회/삭제 통합

MSSQL 환경에서 테이블에 주석을 작성할 수 있는 내장 프로시저가 있다.

sp_addextendedproperty 또는 sp_updateextendedproperty 
뭔가 이름에서 부터 길어서 선뜻 손이 가질 않는다.

https://learn.microsoft.com/ko-kr/sql/relational-databases/system-stored-procedures/sp-addextendedproperty-transact-sql?view=sql-server-ver16

그래서 테이블에 설명에 대해 추가/수정/조회/삭제를 통합한 프로시저를 만들어 보았다. 
프로시저 이름도 매우 짧게 작성했기에 이름이 오묘할 수 있다.

사용자에 맞게 프로시저 이름을 바꿔서 사용하자
그리고 주의할 부분은 로그인 계정의 테이블 스키마 접근 권한을 잘 확인한 이후 사용하자

CREATE PROCEDURE [dbo].[USP_TC]
	@Action			varchar(10),					-- 액션 ADD (or A), DELETE (or D), VIEW(or V), TADD (or TA), TDELETE (or TD)
	@TableName		varchar(100),					-- 테이블 이름
	@ColumnName_Or_Description varchar(100) = NULL,	-- 컬럼 이름 (TA일때는 테이블 설명으로 사영됨)
	@Description	varchar(100) = NULL				-- 컬럼 설명
AS
BEGIN

	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	-- @Action이 'TA'이고 매개변수가 3개일 경우 @ColumnName_Or_Description을 @Description으로 처리
	IF (@Action = 'TADD' OR @Action = 'TA') AND @Description IS NULL
	BEGIN

		SET @Description = @ColumnName_Or_Description
		SET @ColumnName_Or_Description = NULL

	END

	-- 테이블 컬럼 추가일 때
	IF (@Action = 'ADD' OR @Action = 'A')
	BEGIN

		-- 컬럼 코멘트가 존재하는지 확인
		DECLARE @ColValue sql_variant
		SELECT @ColValue = E.value
		FROM SYSOBJECTS A
			INNER JOIN SYSCOLUMNS D ON D.ID = A.ID
			LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES E 
				ON E.MAJOR_ID = D.ID AND E.MINOR_ID = D.COLID AND E.NAME = 'MS_Description'  
		WHERE A.type = 'U'
		  AND A.name = @TableName
		  AND D.name = @ColumnName_Or_Description

		-- 컬럼 코멘트를 추가 또는 수정
		IF @ColValue IS NULL
		BEGIN

			EXEC sp_addextendedproperty 'MS_Description'
										, @Description
										, 'user'
										, 'dbo'
										, 'table'
										, @TableName
										, 'column'
										, @ColumnName_Or_Description

		END
		ELSE
		BEGIN

			EXEC sp_updateextendedproperty 'MS_Description'
										  , @Description
										  , 'user'
										  , 'dbo'
										  , 'table'
										  , @TableName
										  , 'column'
										  , @ColumnName_Or_Description

		END
	END
	ELSE IF (@Action = 'TADD' OR @Action = 'TA')
	BEGIN

		-- 테이블 코멘트가 존재하는지 확인
		IF EXISTS (
			SELECT 1
			FROM sys.extended_properties 
			WHERE major_id = OBJECT_ID(@TableName)
			  AND minor_id = 0
			  AND name = 'MS_Description'
		)
		BEGIN

			-- 테이블 코멘트가 존재하면 업데이트
			EXEC sp_updateextendedproperty 'MS_Description'
										  , @Description
										  , 'user'
										  , 'dbo'
										  , 'table'
										  , @TableName

		END
		ELSE
		BEGIN

			-- 테이블 코멘트가 존재하지 않으면 추가
			EXEC sp_addextendedproperty 'MS_Description'
										, @Description
										, 'user'
										, 'dbo'
										, 'table'
										, @TableName

		END
	END
	ELSE IF (@Action = 'DELETE' OR @Action = 'D')
	BEGIN

		-- 컬럼 코멘트 삭제
		EXEC sp_dropextendedproperty 'MS_Description'
									 , 'user'
									 , 'dbo'
									 , 'table'
									 , @TableName
									 , 'column'
									 , @ColumnName_Or_Description

	END
	ELSE IF (@Action = 'TDELETE' OR @Action = 'TD')
	BEGIN

		-- 테이블 코멘트 삭제
		EXEC sp_dropextendedproperty 'MS_Description'
									 , 'user'
									 , 'dbo'
									 , 'table'
									 , @TableName

	END
	ELSE IF (@Action = 'VIEW' OR @Action = 'V')
	BEGIN

		-- 테이블 및 컬럼 코멘트 조회
		SELECT D.COLORDER					as COLUMN_IDX         
			 , A.NAME						as TABLE_NAME         
			 , ISNULL(C.VALUE ,'')			as TABLE_DESCRIPTION  
			 , D.NAME						as COLUMN_NAME        
			 , ISNULL(E.VALUE ,'')			as COLUMN_DESCRIPTION 
			 , F.DATA_TYPE					as TYPE               
			 , F.CHARACTER_OCTET_LENGTH		as LENGTH             
			 , F.IS_NULLABLE				as IS_NULLABLE        
			 , ISNULL(F.COLLATION_NAME, '') as COLLATION_NAME     
		FROM SYSOBJECTS A 
			INNER JOIN SYSCOLUMNS D 
				ON D.ID = A.ID
			INNER JOIN INFORMATION_SCHEMA.COLUMNS F 
				ON A.NAME = F.TABLE_NAME AND D.NAME = F.COLUMN_NAME
			LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES C 
				ON C.MAJOR_ID = A.ID AND C.MINOR_ID = 0 AND C.NAME = 'MS_Description'
			LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES E 
				ON E.MAJOR_ID = D.ID AND E.MINOR_ID = D.COLID AND E.NAME = 'MS_Description'  
		WHERE A.TYPE = 'U'
		  AND (A.NAME = @TableName OR @TableName = '')	  
		ORDER BY A.NAME, D.COLORDER

	END
	ELSE
	BEGIN
		PRINT '올바르지 않은 작업이 지정되었습니다.'
	END

END

 

 

사용법

-- 컬럼 추가 (A 또는 ADD)
EXEC USP_TC 'A', 'Dog' , 'DogId' , '도그아이디 인듯 싶다4'
EXEC USP_TC 'A', 'Dog' , 'Name' , '개의 이름'
EXEC USP_TC 'A', 'Dog' , 'Address' ,  '개가 사는곳'
EXEC USP_TC 'A', 'Dog' , 'Zipcode' ,  '개집 우편번호'

-- 컬럼 설명 삭제 (D 또는 DEELTE) 
-- Dog테이블의 Zipcode 컬럼 설명을 추가했지만 지워버림
EXEC USP_TC 'D', 'Dog' , 'Zipcode'

-- 테이블 설명 추가 (TA 또는 TADD)
EXEC USP_TC 'TA', 'Dog' ,'도그의테이블'

-- 테이블 설명 삭제
--EXEC USP_TC 'TD', 'Dog' 

-- 테이블 컬럼 정보 조회 (V 또는 VIEW)
EXEC USP_TC 'V', 'Dog'