[MSSQL] 테이블 및 컬럼 설명 수정 프로시져

-- 프로시져 

CREATE PROCEDURE dbo.sp_SetCaption 

(

@Name NVARCHAR(128) = N'Caption',

@SchemaName NVARCHAR(128) = 'dbo',

@TableName NVARCHAR(128) = NULL,

@ColumnName NVARCHAR(128) = NULL,

@Caption NVARCHAR(128) = NULL

)

AS

BEGIN

  SET NOCOUNT ON

IF @Caption IS NULL 

RETURN

IF @TableName IS NULL 

RETURN

IF @ColumnName IS NULL

GOTO GOTO_TABLE

ELSE

GOTO GOTO_COLUMN

GOTO_TABLE:

BEGIN

IF EXISTS( SELECT  *

FROM    ::fn_listextendedproperty (@Name, 'schema', @SchemaName, 'table', @TableName, default, default) )

BEGIN 

EXEC sp_updateextendedproperty

@name = @Name, @value = @Caption,  

@level0Type = N'Schema', @Level0Name = @SchemaName,  

@level1Type = N'Table', @Level1Name = @TableName;  

END

ELSE

BEGIN

EXEC sp_addextendedproperty

@name = @Name, @value = @Caption,  

@level0Type = N'Schema', @Level0Name = @SchemaName,  

@level1Type = N'Table', @Level1Name = @TableName;  

END

RETURN

END

GOTO_COLUMN:

BEGIN

IF EXISTS( SELECT  *

FROM    ::fn_listextendedproperty (@Name, 'schema', @SchemaName, 'table', @TableName, 'column', @ColumnName) )

BEGIN 

EXEC sp_updateextendedproperty

@name = @Name, @value = @Caption,  

@level0Type = N'Schema', @Level0Name = @SchemaName,  

@level1Type = N'Table', @Level1Name = @TableName,

@level2type = N'Column', @level2name = @ColumnName;

END

ELSE

BEGIN

EXEC sp_addextendedproperty

@name = @Name, @value = @Caption,  

@level0Type = N'Schema', @Level0Name = @SchemaName,  

@level1Type = N'Table', @Level1Name = @TableName,

@level2type = N'Column', @level2name = @ColumnName; 

END

RETURN

END

END

---

사용예)

BEGIN

DECLARE @TableName  VARCHAR(128),

@Query NVARCHAR(4000)

-- 테이블명 설정

SET @TableName = 'Test_Table'

--==================================

-- 테이블 존재 여부 확인

IF object_id(N'dbo.' + @TableName) IS NOT NULL

BEGIN

SET @Query = N' DROP TABLE dbo.' + @TableName

EXEC sp_executesql @Query

END

-- 테이블 생성

SET @Query = 

N'

CREATE TABLE dbo.' + @TableName + '

(

TestCode varchar(100),

TestName varchar(100),

TestType varchar(100)

) 

'

EXEC sp_executesql @Query

-- INDEX 생성

SET @Query = 

N'

CREATE UNIQUE INDEX IDX_01 ON dbo.' + @TableName + ' (TestCode)

'

EXEC sp_executesql @Query

-- 설명

EXEC sp_SetCaption @TableName= @TableName, @Caption = '테스트 테이블'

EXEC sp_SetCaption @TableName= @TableName, @ColumnName = 'TestCode', @Caption = '테스트 코드'

EXEC sp_SetCaption @TableName= @TableName, @ColumnName = 'TestName', @Caption = '테스트 명'

EXEC sp_SetCaption @TableName= @TableName, @ColumnName = 'TestType', @Caption = '테스트 타입'

END