-- 특정 테이블명 조회
SELECT OBJTYPE, OBJNAME, NAME, VALUE
FROM ::FN_LISTEXTENDEDPROPERTY (NULL, 'SCHEMA', 'DBO', 'TABLE', 'TBL_NAME', DEFAULT, DEFAULT);

-- 특정 테이블 컬럼명 조회
SELECT OBJTYPE, OBJNAME, NAME, VALUE
FROM ::FN_LISTEXTENDEDPROPERTY(NULL, 'SCHEMA', 'DBO', 'TABLE', 'TBL_NAME', 'COLUMN', DEFAULT);

-- 테이블 코멘트
SELECT  TBL.TABLE_CATALOG                   AS "DATABASE"
,       TBL.TABLE_SCHEMA                    AS "OWNER"
,       TBL.TABLE_NAME                      AS TABLE_NEME
,       CMT.VALUE                           AS COMMENTS

,       'exec sp_addextendedproperty ''MS_Description'', '      + CHAR(39) + ISNULL(CAST(CMT.VALUE AS VARCHAR), '') + CHAR(39) + ', ''USER'' , ''dbo'' , ''TABLE'' , ' + CHAR(39) + TBL.TABLE_NAME + CHAR(39) + ';'     AS ADD_COMMENT
,       'exec sp_updateextendedproperty ''MS_Description'', '   + CHAR(39) + ISNULL(CAST(CMT.VALUE AS VARCHAR), '') + CHAR(39) + ', ''USER'' , ''dbo'' , ''TABLE'' , ' + CHAR(39) + TBL.TABLE_NAME + CHAR(39) + ';'     AS UPDATE_COMMENT
,       'exec sp_dropextendedproperty ''MS_Description'', '     + '''USER'' , ''dbo'' , ''TABLE'' , ' + CHAR(39) + TBL.TABLE_NAME + CHAR(39) + ';'                                                                      AS DROP_COMMENT

FROM INFORMATION_SCHEMA.TABLES          TBL
LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES CMT ON  CMT.MAJOR_ID    = OBJECT_ID(TBL.TABLE_SCHEMA + '.'+ TBL.TABLE_NAME)
                                            AND CMT.MINOR_ID    = 0

ORDER BY TBL.TABLE_SCHEMA, TBL.TABLE_NAME;



-- 컬럼 코멘트
SELECT  COL.TABLE_CATALOG                   AS "DATABASE"
,       COL.TABLE_SCHEMA                    AS "OWNER"
,       COL.TABLE_NAME                      AS TABLE_NAME
,       COL.ORDINAL_POSITION                AS NO
,       COL.COLUMN_NAME
,       COL.DATA_TYPE
,       COL.CHARACTER_MAXIMUM_LENGTH        AS LENGTH
,       COL.IS_NULLABLE
,       COL.COLUMN_DEFAULT                  AS "DEFAULT"
,       CMT.VALUE                           AS COMMENTS

,       'exec sp_addextendedproperty ''MS_Description'', '      + CHAR(39) + ISNULL(CAST(CMT.VALUE AS VARCHAR), '') + CHAR(39) + ', ''USER'' , ''dbo'' , ''TABLE'' , ' + CHAR(39) + COL.TABLE_NAME + CHAR(39) + ', ''COLUMN'' , ' + CHAR(39) + COL.COLUMN_NAME + CHAR(39) + ';'     AS ADD_COMMENT
,       'exec sp_updateextendedproperty ''MS_Description'', '   + CHAR(39) + ISNULL(CAST(CMT.VALUE AS VARCHAR), '') + CHAR(39) + ', ''USER'' , ''dbo'' , ''TABLE'' , ' + CHAR(39) + COL.TABLE_NAME + CHAR(39) + ', ''COLUMN'' , ' + CHAR(39) + COL.COLUMN_NAME + CHAR(39) + ';'     AS UPDATE_COMMENT
,       'exec sp_dropextendedproperty ''MS_Description'', '     + '''USER'' , ''dbo'' , ''TABLE'' , ' + CHAR(39) + COL.TABLE_NAME + CHAR(39) + ', ''COLUMN'' , ' + CHAR(39) + COL.COLUMN_NAME + CHAR(39) + ';'                                                                      AS DROP_COMMENT

FROM INFORMATION_SCHEMA.COLUMNS         COL
JOIN SYS.ALL_COLUMNS                    CLU ON  CLU.OBJECT_ID   = OBJECT_ID(COL.TABLE_SCHEMA + '.' + COL.TABLE_NAME)
                                            AND CLU.NAME        = COL.COLUMN_NAME

LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES CMT ON  CMT.MAJOR_ID    = CLU.OBJECT_ID
                                            AND CMT.MINOR_ID    = CLU.COLUMN_ID
                                            AND CMT.CLASS       = 1

--WHERE CMT.VALUE IS NOT NULL
ORDER BY COL.TABLE_SCHEMA, COL.TABLE_NAME, COL.ORDINAL_POSITION;