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

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

 

1. fn_listextendedproperty

 

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

 

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

view plaincopy to clipboardprint?

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

 

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

view plaincopy to clipboardprint?

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

 

 

2. sp_addextendedproperty,sp_updateextendedproperty

 

- 확장 속성 값 업데이트

 

1) 테이블 개체 확장속성 추가

view plaincopy to clipboardprint?

1. -- 테이블 Caption 확장속성 입력  
2. EXEC    sp_addextendedproperty @name = N'Caption', @value = N'[주석내용입력]',  
3.                     @level0Type = N'Schema', @Level0Name = dbo,  
4.                     @level1Type = N'Table', @Level1Name = '[table명]';  
5.   
6. -- 테이블 Caption 확장속성  수정                   
7. EXEC    sp_updateextendedproperty @name = N'Caption', @value = N'[주석수정입력]',  
8.                     @level0Type = N'Schema', @Level0Name = dbo,  
9.                     @level1Type = N'Table', @Level1Name = '[table명]';  

 

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

view plaincopy to clipboardprint?

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

 

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

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

view plaincopy to clipboardprint?

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

 

 

 3. 응용

 

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

view plaincopy to clipboardprint?

1. SELECT      A.Table_Name,C.Value Table_Description,  
2.             A.Column_Name, A.Data_Type,  
3.             isNULL(CAST(A.Character_Maximum_Length as VARCHAR),  
4.                 CAST(A.Numeric_Precision as VARCHAR) + ',' + CAST(A.Numeric_Scale as VARCHAR)) Column_Length,  
5.             A.Column_Default,   
6.             CASE  
7.             WHEN    A.Is_Nullable   = 'YES' THEN 'Y'  
8.             ELSE    'N'  
9.             END Is_Nullable,  
10.             B.value Colum_Description  
11. FROM        INFORMATION_SCHEMA.ColumnS A  
12. LEFT JOIN   SYS.EXTENDED_PROPERTIES B  
13. ON          B.Major_Id      = OBJECT_ID(A.Table_Name)   
14. AND         B.Minor_Id      = A.Ordinal_Position  
15. LEFT JOIN   (  
16.             SELECT  OBJECT_ID(Objname) Table_Id,Value  
17.             FROM    ::FN_LISTEXTENDEDPROPERTY(NULL, 'user','dbo','Table',NULL, NULL, NULL)  
18.             ) c  
19. ON          C.Table_id  = OBJECT_ID(A.Table_Name)  
20. WHERE       A.Table_Name    = 'bizmallitem'  
21. ORDER BY    A.Table_Name, A.Ordinal_Position   

 

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

view plaincopy to clipboardprint?

1. ----------------------------------------------------------------------  
2. -- Description 넗기  
3. -- 테이블 컬럼 순서대로 Description 입력  
4. -- 테이블 컬럼들과 #TEMP1에 들어가는 Description 의 갯수는 같아야 함  
5. ----------------------------------------------------------------------  
6. CREATE  TABLE   #TEMP1  
7. (  
8.     Seq             INT IDENTITY(1,1),  
9.     Description     VARCHAR(100)  
10. )  
11.   
12. -- INSERT INTO #TEMP1 ( Description ) VALUES ( '설명1' )  
13. -- INSERT INTO #TEMP1 ( Description ) VALUES ( '설명2' )  
14. -- ...  
15. -- INSERT INTO #TEMP1 ( Description ) VALUES ( '설명N' )  
16.   
17. DECLARE     @wIndex         INT  
18. DECLARE     @wMax           INT  
19. DECLARE     @wTableName     VARCHAR(30)  
20. DECLARE     @wColumnName    VARCHAR(30)  
21. DECLARE     @wDescription   VARCHAR(30)  
22.   
23. SELECT      @wTableName = ''  
24. SELECT      @wIndex = 1  
25. SELECT      @wMax       = MAX(Ordinal_Position)  
26. FROM        INFORMATION_SCHEMA.ColumnS  
27.   
28. WHILE ( @wIndex <= @wMax )  
29. BEGIN  
30.       
31.     SELECT      @wColumnName        = ''  
32.     SELECT      @wDescription       = ''  
33.       
34.     SELECT      @wColumnName        = Column_Name  
35.     FROM        INFORMATION_SCHEMA.ColumnS  
36.     WHERE       Table_Name          = @wTableName  
37.     AND         Ordinal_Position    = @wIndex  
38.       
39.     SELECT      @wDescription       = Description  
40.     FROM        #TEMP1  
41.     WHERE       Seq                 = @wIndex  
42.       
43.     EXEC    sp_addextendedproperty @name = N'MS_Description', @value = @wDescription,  
44.                     @level0type = N'Schema', @level0name = dbo,  
45.                     @level1type = N'Table',  @level1name = @wTableName,  
46.                     @level2type = N'Column', @level2name = @wColumnName;      
47.     GO;  
48.               
49.     SELECT      @wIndex = @wIndex + 1  
50.       
51. END  

 

출처 : http://dexcore.tistory.com/admin/entry/post