MySQL 서버에 생성된 모든 데이터베이스의 목록과, 각 데이터베이스에 속한 모든 테이블을 한꺼번에 확인하려면 INFORMATION_SCHEMA를 활용하는 것이 가장 효과적입니다. SHOW DATABASES나 SHOW TABLES 명령은 데이터베이스마다 따로 실행해야 하지만, 아래 쿼리 하나만으로 서버 전체의 구조를 한눈에 파악할 수 있습니다.
기본 문법
SELECT my_schema.SCHEMA_NAME, GROUP_CONCAT(tbl.TABLE_NAME)
FROM information_schema.SCHEMATA my_schema
LEFT JOIN information_schema.TABLES tbl
ON my_schema.SCHEMA_NAME = tbl.TABLE_SCHEMA
GROUP BY my_schema.SCHEMA_NAME;
information_schema.SCHEMATA에는 서버의 모든 데이터베이스 정보가, information_schema.TABLES에는 각 데이터베이스의 테이블 정보가 저장되어 있습니다. 두 테이블을 SCHEMA_NAME = TABLE_SCHEMA 조건으로 조인한 뒤, GROUP_CONCAT() 함수로 테이블 이름들을 하나의 문자열로 묶어 출력하는 원리입니다.
쿼리 실행 예시
mysql> SELECT my_schema.SCHEMA_NAME, GROUP_CONCAT(tbl.TABLE_NAME)
-> FROM information_schema.SCHEMATA my_schema
-> LEFT JOIN information_schema.TABLES tbl
-> ON my_schema.SCHEMA_NAME = tbl.TABLE_SCHEMA
-> GROUP BY my_schema.SCHEMA_NAME;
실행 결과
위 쿼리를 실행하면 데이터베이스별 테이블 목록이 한 줄씩 출력됩니다. 실제 결과는 매우 길기 때문에 여기서는 주요 부분만 발췌했습니다.
+----------------------+------------------------------------------------------------------+ | SCHEMA_NAME | GROUP_CONCAT(tbl.TABLE_NAME) | +----------------------+------------------------------------------------------------------+ | bothinnodbandmyisam | employee,gradedemo,student,student_information | | business | addconstraintdemo,addonedaydemo,autoincrementtozero,college,... | | commandline | caseinsensitivedistinctdemo,insertmaxplus1demo,instructor,... | | customer-tracker | NULL | | customertracker | NULL | | demo | mytable | | education | student,university | | hb_student_tracker | demotable194,demotable202,demotable210,student,... | | information_schema | COLUMNS,ENGINES,VIEWS,TABLES,SCHEMATA,TRIGGERS,STATISTICS,... | | mysql | db,user,tables_priv,time_zone,general_log,slow_log,plugin,... | | performance_schema | accounts,threads,global_status,session_variables,... | | rdb | boy,girl | | sample | accumulateddemo,autoincrementdemo,changecolumnname,... | | sys | host_summary_by_file_io,user_summary,schema_object_overview,... | | test | customers,dateformatdemo,decimaldemo,duplicaterecords,... | | tracker | preventnegativenumbers | | universitydatabase | NULL | | web | demotable492,demotable500,DemoTable,view_demotable388,... | | webtracker | NULL | +----------------------+------------------------------------------------------------------+ 36 rows in set, 6 warnings (0.18 sec)
테이블이 하나도 없는 데이터베이스는 NULL로 표시됩니다. 이는 내부 조인(INNER JOIN)이 아닌 LEFT JOIN을 사용했기 때문으로, 덕분에 비어 있는 데이터베이스도 결과 목록에서 누락되지 않습니다.
알아두면 좋은 팁
- GROUP_CONCAT 길이 제한 해제:
GROUP_CONCAT()은 기본적으로 1,024바이트까지만 문자열을 이어 붙입니다. 그래서 business, sample처럼 테이블이 많은 데이터베이스의 목록은 중간에 잘려서 보일 수 있습니다. 이 경우 세션 시작 시 다음 명령으로 제한을 늘려 주세요.SET SESSION group_concat_max_len = 1000000; - 특정 데이터베이스만 조회:
WHERE my_schema.SCHEMA_NAME = '데이터베이스명'조건을 추가하면 원하는 데이터베이스의 테이블 목록만 확인할 수 있습니다. - 테이블 개수 함께 보기: SELECT 절에
COUNT(tbl.TABLE_NAME)을 추가하면 각 데이터베이스에 테이블이 몇 개 있는지도 한 번에 파악할 수 있습니다.