데이터베이스를 관리하다 보면 특정 데이터베이스에 존재하는 테이블 중 실제로 데이터가 들어 있는(비어 있지 않은) 테이블만 확인하고 싶은 경우가 있습니다. 이럴 때 information_schema를 활용하면 간단하게 해결할 수 있습니다.
기본 문법
특정 MySQL 데이터베이스에서 비어 있지 않은 테이블 목록을 가져오려면 아래와 같은 쿼리를 사용합니다.
SELECT table_type, table_name, table_schema FROM information_schema.tables WHERE table_rows >= 1 AND table_schema = 'yourDatabaseName';
핵심은 information_schema.tables라는 시스템 뷰를 조회하는 것입니다. 여기서 table_rows >= 1 조건을 추가하면 행이 하나 이상 존재하는 테이블, 즉 비어 있지 않은 테이블만 필터링됩니다.
- table_type: 테이블 유형(BASE TABLE, VIEW 등)
- table_name: 테이블 이름
- table_schema: 테이블이 속한 데이터베이스 이름
실제 실행 예제
예제 데이터베이스로 "test"를 사용해 보겠습니다. 위 문법을 적용한 쿼리는 다음과 같습니다.
mysql> SELECT table_type, table_name, table_schema FROM information_schema.tables -> WHERE table_rows >= 1 AND table_schema = 'test';
쿼리를 실행하면 "test" 데이터베이스에 있는 비어 있지 않은 테이블들이 아래와 같이 출력됩니다.
+------------+------------------------------+--------------+| TABLE_TYPE | TABLE_NAME | TABLE_SCHEMA |+------------+------------------------------+--------------+| BASE TABLE | add30minutesdemo | test || BASE TABLE | addoneday | test || BASE TABLE | agecalculatesdemo | test || BASE TABLE | aliasdemo | test || BASE TABLE | allcharacterbeforespace | test || BASE TABLE | allownulldemo | test || BASE TABLE | autoincrementdemo | test || BASE TABLE | betweendatedemo | test || BASE TABLE | bookdatedemo | test || BASE TABLE | changecolumnpositiondemo | test || BASE TABLE | concatenatetwocolumnsdemo | test || BASE TABLE | cumulativesumdemo | test || BASE TABLE | currentdatetimedemo | test || BASE TABLE | dateasstringdemo | test || BASE TABLE | dateformatdemo | test || BASE TABLE | dateinsertdemo | test || BASE TABLE | datesofoneweek | test || BASE TABLE | datetimedemo | test || BASE TABLE | dayofweekdemo | test || BASE TABLE | decimaltointdemo | test || BASE TABLE | defaultdemo | test || BASE TABLE | deletemanyrows | test || BASE TABLE | differencetimestamp | test || BASE TABLE | distinctdemo | test || BASE TABLE | employee | test || BASE TABLE | employeedesignation | test || BASE TABLE | findlowercasevalue | test || BASE TABLE | generatingnumbersdemo | test || BASE TABLE | gmailsignin | test || BASE TABLE | groupbytwofieldsdemo | test || BASE TABLE | groupmonthandyeardemo | test || BASE TABLE | highestnumberdemo | test || BASE TABLE | ifnulldemo | test || BASE TABLE | insertignoredemo | test || BASE TABLE | insertwithmultipleandsigle | test || BASE TABLE | int11demo | test || BASE TABLE | intvsintanythingdemo | test || BASE TABLE | lasttwocharacters | test || BASE TABLE | likebinarydemo | test || BASE TABLE | likedemo | test || BASE TABLE | maxlengthfunctiondemo | test || BASE TABLE | newtableduplicate | test || BASE TABLE | notequalsdemo | test || BASE TABLE | nowandcurdatedemo | test || BASE TABLE | nthrecorddemo | test || BASE TABLE | nullandemptydemo | test || BASE TABLE | orderbycharacterlength | test || BASE TABLE | orderbynullfirstdemo | test || BASE TABLE | orderindemo | test || BASE TABLE | originaltable | test || BASE TABLE | parsedatedemo | test || BASE TABLE | passinganarraydemo | test || BASE TABLE | prependstringoncolumnname | test || BASE TABLE | pricedemo | test || BASE TABLE | queryresultdemo | test || BASE TABLE | replacedemo | test || BASE TABLE | rowexistdemo | test || BASE TABLE | rowpositiondemo | test || BASE TABLE | rowwithsamevalue | test || BASE TABLE | safedeletedemo | test || BASE TABLE | searchtextdemo | test || BASE TABLE | selectdataonyearandmonthdemo | test || BASE TABLE | selectdistincttwocolumns | test || BASE TABLE | skiplasttenrecords | test || BASE TABLE | stringreplacedemo | test || BASE TABLE | stringtodate | test || BASE TABLE | student | test || BASE TABLE | studentdemo | test || BASE TABLE | studentmodifytabledemo | test || BASE TABLE | studenttable | test || BASE TABLE | subtract3hours | test || BASE TABLE | temporarycolumnwithvaluedemo | test || BASE TABLE | timetosecond | test || BASE TABLE | timetoseconddemo | test || BASE TABLE | toggledemo | test || BASE TABLE | toogledemo | test || BASE TABLE | updatevalueincrementally | test || BASE TABLE | wheredemo | test || BASE TABLE | wholewordmatchdemo | test || BASE TABLE | zipcodepadwithzerodemo | test |+------------+------------------------------+--------------+80 rows in set (0.00 sec)
실행 결과를 보면 총 80개의 테이블이 조회되었으며, 모두 BASE TABLE 유형이고 "test" 스키마에 속해 있는 것을 확인할 수 있습니다.
참고 사항
table_rows 컬럼의 값은 저장 엔진에 따라 정확도가 다를 수 있다는 점에 유의해야 합니다.
- MyISAM: 정확한 행 수를 저장하므로 신뢰할 수 있습니다.
- InnoDB: 근사치(approximate) 값을 반환합니다. 따라서 대용량 테이블에서는 실제 행 수와 차이가 있을 수 있으며, 최근 변경 사항이 반영되지 않았을 수도 있습니다.
따라서 InnoDB 환경에서 정확한 결과가 필요하다면, 이 쿼리로 후보 테이블을 추린 후 SELECT COUNT(*)로 개별 검증하는 방식을 권장합니다.