[MSSQL] 테이블,컬럼 개체 확장속성 보기 및 등록

테이블,컬럼 개체 설명 보기 및 등록

 

1. fn_listextendedproperty

 

- 데이터베이스 개체의 확장 속성 값 반환

 

1) 테이블 개체 설명(주석)

SELECT objtype, objname, name, value FROM ::fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', default, default, default);

 

2) 테이블 컬럼 개체 설명(주석)

SELECT objtype, objname, name, value FROM ::fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', '[table명]', 'column', default);

 

 

2. sp_addextendedproperty,sp_updateextendedproperty

 

- 확장 속성 값 업데이트

 

1) 테이블 개체 확장속성 추가-- 테이블 Caption 확장속성 입력 EXEC sp_addextendedproperty @name = N'Caption', @value = N'[주석내용입력]', @level0Type = N'Schema', @Level0Name = dbo, @level1Type = N'Table', @Level1Name = '[table명]'; -- 테이블 Caption 확장속성 수정 EXEC sp_updateextendedproperty @name = N'Caption', @value = N'[주석수정입력]', @level0Type = N'Schema', @Level0Name = dbo, @level1Type = N'Table', @Level1Name = '[table명]';

 

2) 테이블 컬럼 개체 확장속성 업데이트

--테이블 특정 컬럼에 확장속성 추가 EXEC sp_addextendedproperty @name = N'Caption', @value = '[주석내용입력]', @level0type = N'Schema', @level0name = dbo, @level1type = N'Table', @level1name = '[table명]', @level2type = N'Column', @level2name = '[Column명]'; --테이블 특정 컬럼에 확장속성 추가 EXEC sp_updateextendedproperty @name = N'Caption', @value = '[주석수정입력]', @level0type = N'Schema', @level0name = dbo, @level1type = N'Table', @level1name = '[table명]', @level2type = N'Column', @level2name = '[Column명]';

 

3) 테이블 컬럼 개체 설명 항목으로 추가(업데이트)

- name = N'MS_Description' 으로 변경하면 컬럼 설명쪽에도 추가(업데이트), 확장속성에도 추가(업데이트)됨

--테이블 특정 컬럼에 확장속성 추가 EXEC sp_addextendedproperty @name = N'MS_Description', @value = '[주석내용입력]', @level0type = N'Schema', @level0name = dbo, @level1type = N'Table', @level1name = '[table명]', @level2type = N'Column', @level2name = '[Column명]'; --테이블 특정 컬럼에 확장속성 추가 EXEC sp_updateextendedproperty @name = N'MS_Description', @value = '[주석수정입력]', @level0type = N'Schema', @level0name = dbo, @level1type = N'Table', @level1name = '[table명]', @level2type = N'Column', @level2name = '[Column명]';

 

 

 3. 응용

 

1) 테이블 Description, 컬럼명, DataType, Column_Length, Column_Default, Is_Nullable, 컬럼 Description 가져오기

SELECT A.Table_Name,C.Value Table_Description, A.Column_Name, A.Data_Type, isNULL(CAST(A.Character_Maximum_Length as VARCHAR), CAST(A.Numeric_Precision as VARCHAR) + ',' + CAST(A.Numeric_Scale as VARCHAR)) Column_Length, A.Column_Default, CASE WHEN A.Is_Nullable = 'YES' THEN 'Y' ELSE 'N' END Is_Nullable, B.value Colum_Description FROM INFORMATION_SCHEMA.ColumnS A LEFT JOIN SYS.EXTENDED_PROPERTIES B ON B.Major_Id = OBJECT_ID(A.Table_Name) AND B.Minor_Id = A.Ordinal_Position LEFT JOIN ( SELECT OBJECT_ID(Objname) Table_Id,Value FROM ::FN_LISTEXTENDEDPROPERTY(NULL, 'user','dbo','Table',NULL, NULL, NULL) ) c ON C.Table_id = OBJECT_ID(A.Table_Name) WHERE A.Table_Name = 'bizmallitem' ORDER BY A.Table_Name, A.Ordinal_Position

 

2) 테이블 컬럼 설명 편리한 방법으로 넣기 ( 임시테이블 이용 )

---------------------------------------------------------------------- -- Description 넗기 -- 테이블 컬럼 순서대로 Description 입력 -- 테이블 컬럼들과 #TEMP1에 들어가는 Description 의 갯수는 같아야 함 ---------------------------------------------------------------------- CREATE TABLE #TEMP1 ( Seq INT IDENTITY(1,1), Description VARCHAR(100) ) -- INSERT INTO #TEMP1 ( Description ) VALUES ( '설명1' ) -- INSERT INTO #TEMP1 ( Description ) VALUES ( '설명2' ) -- ... -- INSERT INTO #TEMP1 ( Description ) VALUES ( '설명N' ) DECLARE @wIndex INT DECLARE @wMax INT DECLARE @wTableName VARCHAR(30) DECLARE @wColumnName VARCHAR(30) DECLARE @wDescription VARCHAR(30) SELECT @wTableName = '' SELECT @wIndex = 1 SELECT @wMax = MAX(Ordinal_Position) FROM INFORMATION_SCHEMA.ColumnS WHILE ( @wIndex <= @wMax ) BEGIN SELECT @wColumnName = '' SELECT @wDescription = '' SELECT @wColumnName = Column_Name FROM INFORMATION_SCHEMA.ColumnS WHERE Table_Name = @wTableName AND Ordinal_Position = @wIndex SELECT @wDescription = Description FROM #TEMP1 WHERE Seq = @wIndex EXEC sp_addextendedproperty @name = N'MS_Description', @value = @wDescription, @level0type = N'Schema', @level0name = dbo, @level1type = N'Table', @level1name = @wTableName, @level2type = N'Column', @level2name = @wColumnName; GO; SELECT @wIndex = @wIndex + 1 END

 

 

 

< 테이블 확장 속성 >

 

< 컬럼 확장 속성 > 

 

< 컬럼 설명 >