MySQL에서 특정 데이터베이스의 테이블 상세 정보를 조회하는 저장 프로시저 만들기

개요

MySQL에서는 INFORMATION_SCHEMA.TABLES 뷰를 활용하면 특정 데이터베이스에 포함된 모든 테이블의 상세 정보를 한 번에 조회할 수 있습니다. 이 조회 로직을 저장 프로시저(Stored Procedure)로 만들어 두면, 필요할 때마다 간단한 CALL 문만으로 반복해서 확인할 수 있어 매우 편리합니다.

예시 데이터베이스 확인

현재 'query'라는 이름의 데이터베이스를 사용 중이며, 해당 데이터베이스에는 다음과 같은 두 개의 테이블이 존재한다고 가정해 보겠습니다.

mysql> Show tables in query;
+-----------------+
| Tables_in_query |
+-----------------+
| student_detail  |
| student_info    |
+-----------------+
2 rows in set (0.00 sec)

저장 프로시저 생성

다음은 'query' 데이터베이스의 테이블 목록을 상세 정보와 함께 반환하는 저장 프로시저입니다.

mysql> DELIMITER//
mysql> CREATE procedure tablelist()
    -> BEGIN
    -> Select * from Information_schema.tables WHERE table_schema = 'query';
    -> END //

Query OK, 0 rows affected (0.06 sec)
mysql> DELIMITER;

프로시저 내부에서는 Information_schema.tables에서 table_schema 값이 'query'인 행들을 모두 조회하도록 작성되었습니다. INFORMATION_SCHEMA는 MySQL 서버가 관리하는 메타데이터, 즉 테이블 구조, 스토리지 엔진, 행 수, 생성 시각 등의 정보를 담고 있는 시스템 스키마입니다.

프로시저 호출 및 결과 확인

CALL tablelist() 명령으로 프로시저를 실행하면 아래와 같이 각 테이블의 상세 정보가 출력됩니다.

mysql> CALL tablelist()\G
*************************** 1. row ***************************
    TABLE_CATALOG: def
     TABLE_SCHEMA: query
       TABLE_NAME: student_detail
       TABLE_TYPE: BASE TABLE
           ENGINE: InnoDB
          VERSION: 10
       ROW_FORMAT: Dynamic
       TABLE_ROWS: 4
   AVG_ROW_LENGTH: 4096
      DATA_LENGTH: 16384
  MAX_DATA_LENGTH: 0
     INDEX_LENGTH: 0
        DATA_FREE: 0
    AUTO_INCREMENT: NULL
       CREATE_TIME: 2017-12-13 16:25:44
       UPDATE_TIME: NULL
        CHECK_TIME: NULL
   TABLE_COLLATION: latin1_swedish_ci
         CHECKSUM: NULL
    CREATE_OPTIONS:
      TABLE_COMMENT:

*************************** 2. row ***************************
    TABLE_CATALOG: def
     TABLE_SCHEMA: query
       TABLE_NAME: student_info
       TABLE_TYPE: BASE TABLE
           ENGINE: InnoDB
          VERSION: 10
       ROW_FORMAT: Dynamic
       TABLE_ROWS: 4
   AVG_ROW_LENGTH: 4096
      DATA_LENGTH: 16384
  MAX_DATA_LENGTH: 0
     INDEX_LENGTH: 0
        DATA_FREE: 0
    AUTO_INCREMENT: NULL
       CREATE_TIME: 2017-12-12 09:52:51
       UPDATE_TIME: NULL
        CHECK_TIME: NULL
   TABLE_COLLATION: latin1_swedish_ci
         CHECKSUM: NULL
    CREATE_OPTIONS:
      TABLE_COMMENT:
2 rows in set (0.01 sec)

Query OK, 0 rows affected (0.06 sec)

주요 컬럼 설명

TABLE_SCHEMA / TABLE_NAME: 테이블이 속한 데이터베이스 이름과 테이블 이름
ENGINE: 테이블에 사용된 스토리지 엔진(InnoDB 등)
TABLE_ROWS: 테이블에 저장된 대략적인 행(row) 수
DATA_LENGTH / INDEX_LENGTH: 데이터와 인덱스가 차지하는 디스크 용량
CREATE_TIME / UPDATE_TIME: 테이블 생성 시각과 마지막 수정 시각
TABLE_COLLATION: 테이블에 적용된 문자셋 비교 규칙(Collation)

마무리

이처럼 저장 프로시저를 활용하면 복잡한 메타데이터 조회 쿼리를 매번 직접 작성할 필요 없이, 하나의 명령만으로 데이터베이스 전체 테이블의 구조와 상태를 손쉽게 점검할 수 있습니다. 여러 데이터베이스를 관리하는 환경에서 테이블 용량, 엔진 종류, 최근 변경 이력 등을 주기적으로 확인할 때 특히 유용하게 활용할 수 있습니다.