MySQL에서 모든 데이터베이스의 비어 있지 않은 테이블 목록 조회하기
MySQL 서버에 존재하는 수많은 테이블 중 실제로 데이터가 들어 있는 테이블, 즉 비어 있지 않은(non-empty) 테이블만 골라서 확인하고 싶다면 information_schema.tables를 활용하는 것이 가장 간편합니다. 이 메타데이터 뷰에는 모든 데이터베이스의 테이블 정보와 함께 대략적인 행 수(table_rows)가 기록되어 있기 때문입니다.
기본 쿼리
아래 쿼리는 전체 데이터베이스를 대상으로 행이 1개 이상 존재하는 테이블만 필터링하여 보여 줍니다.
mysql> SELECT table_schema, table_type, table_name
-> FROM information_schema.tables
-> WHERE table_rows >= 1;
table_rows >= 1 조건 덕분에 결과에는 빈 테이블이 제외되고, 하나 이상의 레코드를 가진 테이블만 출력됩니다. 어떤 데이터베이스에 속한 테이블인지 바로 알 수 있도록 table_schema(데이터베이스명) 컬럼도 함께 조회했습니다.
실행 결과 예시
쿼리를 실행하면 다음과 같은 형태의 결과가 반환됩니다. 시스템 데이터베이스(mysql, performance_schema, sys 등)의 내부 테이블과 사용자가 생성한 일반 테이블이 함께 나타나는 것을 확인할 수 있습니다.
+--------------------+------------+----------------------+ | TABLE_SCHEMA | TABLE_TYPE | TABLE_NAME | +--------------------+------------+----------------------+ | mysql | BASE TABLE | innodb_table_stats | | mysql | BASE TABLE | innodb_index_stats | | performance_schema | BASE TABLE | cond_instances | | performance_schema | BASE TABLE | events_waits_current | | performance_schema | BASE TABLE | events_waits_history | | ... | ... | ... | | sys | BASE TABLE | sys_config | | sample | BASE TABLE | student | | sample | BASE TABLE | product | | sample | BASE TABLE | demowhere | +--------------------+------------+----------------------+
실제 환경에서는 시스템 테이블을 포함해 수백 개의 테이블이 출력될 수 있으므로, 위 예시에서는 이해를 돕기 위해 일부만 발췌했습니다.
특정 데이터베이스만 조회하기
전체 데이터베이스가 아니라 특정 데이터베이스의 비어 있지 않은 테이블만 보고 싶다면 WHERE 절에 table_schema 조건을 추가하면 됩니다.
mysql> SELECT table_name, table_rows
-> FROM information_schema.tables
-> WHERE table_schema = 'your_database_name'
-> AND table_rows >= 1;
알아두면 좋은 점
- InnoDB 테이블의 행 수는 근사치입니다.
table_rows컬럼은 InnoDB 스토리지 엔진의 통계 정보를 기반으로 하므로 실제 행 수와 다소 차이가 날 수 있습니다. 정확한 개수가 필요하다면 해당 테이블에 대해SELECT COUNT(*)를 직접 실행해야 합니다. - 통계 정보는 갱신할 수 있습니다. 최근에 대량의 데이터가 변경되었다면
ANALYZE TABLE 테이블명;을 실행해 통계를 새로 고친 뒤 조회하는 것이 좋습니다. - 빈 테이블 찾기도 가능합니다. 조건을
table_rows = 0또는table_rows IS NULL로 바꾸면 반대로 비어 있는 테이블 목록을 얻을 수 있습니다.
마무리
information_schema.tables와 table_rows 조건만 활용하면 별도의 스크립트 없이 SQL 한 문장으로 전체 데이터베이스의 비어 있지 않은 테이블을 손쉽게 파악할 수 있습니다. 데이터베이스 정리, 용량 점검, 스키마 감사 등 다양한 상황에서 유용하게 활용해 보시기 바랍니다.