Mysql 시스템 참조 쿼리 (mysql object select)

-- 테이블 정보
SELECT * FROM INFORMATION_SCHEMA.TABLES;

-- 세부 컬럼정보
SELECT * FROM INFORMATION_SCHEMA.COLUMNS;

-- 활용예제
SELECT
TABLE_NAME AS '테이블 명'
,(SELECT TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = A.TABLE_NAME LIMIT 1 ) AS '테이블 주석'
,COLUMN_NAME AS '컬럼명'
,COLUMN_COMMENT AS '주석'
,COLUMN_TYPE
,SUBSTRING(IS_NULLABLE,1) AS 'NULL 여부'
,(case when length(COLUMN_KEY) > 0 THEN 'Y' ELSE 'N' END) AS 'PK 여부' 
 FROM INFORMATION_SCHEMA.COLUMNS A WHERE TABLE_SCHEMA = 'TESTDB' order by TABLE_NAME;