MySQL에서 데이터베이스에 속한 모든 테이블의 구조를 테이블별로 하나씩 DESCRIBE 명령어를 실행하지 않고도, 단 하나의 SQL 문장으로 전체 컬럼 정보를 확인할 수 있습니다. 이때 활용하는 것이 바로 INFORMATION_SCHEMA.COLUMNS 뷰입니다.
기본 문법
INFORMATION_SCHEMA.COLUMNS는 MySQL 서버가 관리하는 메타데이터(시스템 카탈로그)로, 모든 데이터베이스와 테이블의 컬럼 정보를 담고 있습니다. 아래와 같은 형식으로 조회하면 됩니다.
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'yourDatabaseName'\G
여기서 TABLE_SCHEMA에는 대상 데이터베이스 이름을 지정합니다. 마지막의 \G는 결과를 세로 방향(행 단위)으로 출력해 가독성을 높여주는 옵션입니다.
예제: 'sample' 데이터베이스 조회하기
설명을 위해 두 개의 테이블(mytable, yourtable)을 포함한 sample 데이터베이스를 사용하겠습니다.
- mytable
- yourtable
위 문법을 실제 데이터베이스에 적용한 쿼리는 다음과 같습니다.
mysql> SELECT * FROM information_schema.columns WHERE table_schema = 'sample'\G
실행 결과
아래 출력 결과를 보면 두 테이블에 대한 컬럼 정보가 한 번에 조회된 것을 확인할 수 있습니다.
*************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: sample TABLE_NAME: mytable COLUMN_NAME: id ORDINAL_POSITION: 1 COLUMN_DEFAULT: NULL IS_NULLABLE: YES DATA_TYPE: int CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: 10 NUMERIC_SCALE: 0 DATETIME_PRECISION: NULL CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL COLUMN_TYPE: int(11) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,references COLUMN_COMMENT: GENERATION_EXPRESSION: SRS_ID: NULL *************************** 2. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: sample TABLE_NAME: mytable COLUMN_NAME: Name ORDINAL_POSITION: 2 COLUMN_DEFAULT: NULL IS_NULLABLE: YES DATA_TYPE: varchar CHARACTER_MAXIMUM_LENGTH: 100 CHARACTER_OCTET_LENGTH: 400 NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_NAME: utf8mb4 COLLATION_NAME: utf8mb4_0900_ai_ci COLUMN_TYPE: varchar(100) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,references COLUMN_COMMENT: GENERATION_EXPRESSION: SRS_ID: NULL *************************** 3. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: sample TABLE_NAME: yourtable COLUMN_NAME: id ORDINAL_POSITION: 1 COLUMN_DEFAULT: NULL IS_NULLABLE: YES DATA_TYPE: int CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: 10 NUMERIC_SCALE: 0 DATETIME_PRECISION: NULL CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL COLUMN_TYPE: int(11) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,references COLUMN_COMMENT: GENERATION_EXPRESSION: SRS_ID: NULL *************************** 4. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: sample TABLE_NAME: yourtable COLUMN_NAME: YourName ORDINAL_POSITION: 2 COLUMN_DEFAULT: NULL IS_NULLABLE: YES DATA_TYPE: varchar CHARACTER_MAXIMUM_LENGTH: 100 CHARACTER_OCTET_LENGTH: 400 NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_NAME: utf8mb4 COLLATION_NAME: utf8mb4_0900_ai_ci COLUMN_TYPE: varchar(100) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,references COLUMN_COMMENT: GENERATION_EXPRESSION: SRS_ID: NULL 4 rows in set (0.00 sec)
주요 출력 항목 살펴보기
결과에 포함된 주요 컬럼은 다음과 같습니다.
- TABLE_NAME / COLUMN_NAME: 테이블명과 컬럼명
- ORDINAL_POSITION: 해당 컬럼이 테이블 내에서 차지하는 순번
- DATA_TYPE / COLUMN_TYPE: 데이터 타입(int, varchar 등) 및 정확한 타입 정의
- IS_NULLABLE: NULL 값 허용 여부
- COLUMN_KEY: PRI(기본키), UNI(유니크), MUL(인덱스) 등 키 정보
- CHARACTER_SET_NAME / COLLATION_NAME: 문자열 컬럼의 문자셋과 콜레이션
마무리
이처럼 INFORMATION_SCHEMA.COLUMNS를 사용하면 여러 테이블의 구조를 한 번의 쿼리로 파악할 수 있어, 스키마 분석이나 문서화 작업 시 매우 유용합니다. 필요하다면 WHERE 절에 TABLE_NAME 조건을 추가하거나, 조회하고 싶은 컬럼만 선택하여 더 간결한 결과를 얻을 수도 있습니다.