--임시테이블, 커서, 코멘트 조회 펑션 등을 이용

--전 데이터베이스 의 테이블, 컬럼 조회 구문을 작성했습니다.

--필요하신분 실행해보시고 코멘트주시면 감사하겠습니다.

/*****************************************************************************
AUTHOR      : <Joe>
CREATE DATE : <2015-09-10 14:00:01>
DESCRIPTION : <전체 DATABASE의 TABLE 및 COLUMN COMMENT 출력, MSSQL>            
 *****************************************************************************/
IF OBJECT_ID('tempdb..#DBS_TABLES_COLUMNS_COMMENTS') IS NOT NULL
DROP TABLE #DBS_TABLES_COLUMNS_COMMENTS

--임시테이블생성:
CREATE TABLE #DBS_TABLES_COLUMNS_COMMENTS
(
    [DATABASE]       VARCHAR(100) 
  , [TABLE]          VARCHAR(100) 
  , [TABLE_COMMENT]  VARCHAR(100)
  , [COLUMN]         VARCHAR(100)
  , [COLUMN_COMMENT] VARCHAR(100)
)

--변수선언: DB명
DECLARE @DB_NAME NVARCHAR(100);

--커서선언
DECLARE CURSORDBS CURSOR
    FOR SELECT NAME
          FROM SYS.DATABASES
         WHERE NAME NOT IN (
                             'UNNECESSARY DATABASE NAME(TO SELECT COMMENT)'
                            ,'MASTER'
                            ,'TEMPDB'
                            ,'MODEL'
                            ,'MSDB'

                            ) order by name;
--커서오픈
OPEN CURSORDBS;

FETCH NEXT FROM CURSORDBS INTO @DB_NAME;

--첫번째 데이터를 불러옴(FETCH)
WHILE(@@FETCH_STATUS <> -1)
BEGIN

--작업 수행      
DECLARE @sql NVARCHAR(MAX);

SET @sql =
' INSERT INTO #DBS_TABLES_COLUMNS_COMMENTS                                                      '+
' SELECT '''+@DB_NAME+'''                                                              '+
'      , AA.TABLE_NAME                                                                 '+
'      , (SELECT CONVERT(VARCHAR,VALUE) FROM '+@DB_NAME+'.SYS.'+'FN_LISTEXTENDEDPROPERTY (NULL, '+'''SCHEMA'''+', '+'''DBO'''+', '+'''TABLE'''+', '+'AA.TABLE_NAME'+', DEFAULT, DEFAULT))'+
'      , BB.COLUMN_NAME                                                 '+
'      , (SELECT CONVERT(VARCHAR,VALUE) FROM '+@DB_NAME+'.SYS.'+'FN_LISTEXTENDEDPROPERTY (NULL, '+'''SCHEMA'''+', '+'''DBO'''+', '+'''TABLE'''+', '+'AA.TABLE_NAME'+', ''COLUMN'', BB.COLUMN_NAME))'+
'   FROM '+@DB_NAME+'.INFORMATION_SCHEMA.TABLES  AA INNER JOIN                   '+
'        '+@DB_NAME+'.INFORMATION_SCHEMA.COLUMNS BB ON AA.TABLE_NAME = BB.TABLE_NAME   '+
'  WHERE TABLE_TYPE= ''BASE TABLE''                                        '+
'  ORDER BY AA.TABLE_NAME                                                '+
'         , BB.ORDINAL_POSITION                                           '
;  

EXEC(@sql);

    --다음 결과 데이터로 이동    
    FETCH NEXT FROM CURSORDBS INTO @DB_NAME;
END
--커서를 닫고 메모리 반환
CLOSE CURSORDBS;
DEALLOCATE CURSORDBS;
SELECT * FROM #DBS_TABLES_COLUMNS_COMMENTS; 

IF OBJECT_ID('tempdb..#DBS_TABLES_COLUMNS_COMMENTS') IS NOT NULL
DROP TABLE #DBS_TABLES_COLUMNS_COMMENTS