MySQL에서 모든 테이블에 존재하는 열(컬럼)을 찾는 방법 완벽 가이드

데이터베이스를 운영하다 보면 수십, 수백 개의 테이블 중에서 특정 컬럼명이 어느 테이블에 존재하는지 확인해야 할 때가 있습니다. MySQL에서는 INFORMATION_SCHEMA.COLUMNS를 활용하면 현재 데이터베이스의 모든 테이블을 대상으로 특정 열의 존재 여부를 손쉽게 조회할 수 있습니다.

기본 문법

컬럼명을 식별하려면 아래와 같은 쿼리 구문을 사용합니다.

SELECT table_name, column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = SCHEMA()
AND column_name = '찾으려는컬럼명';

여기서 SCHEMA() 함수는 현재 접속 중인 데이터베이스 이름을 반환하므로, 별도로 데이터베이스명을 입력하지 않아도 됩니다.

실전 예제: EmployeeAge 컬럼 찾기

실제로 위 쿼리를 실행하여 모든 테이블에서 EmployeeAge라는 컬럼이 존재하는지 확인해 보겠습니다.

mysql> SELECT table_name, column_name
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE table_schema = SCHEMA()
    -> AND column_name = 'EmployeeAge';

쿼리를 실행하면 'EmployeeAge' 컬럼을 포함하고 있는 테이블 목록이 다음과 같이 출력됩니다.

+---------------+-------------+
| TABLE_NAME    | COLUMN_NAME |
+---------------+-------------+
| demotable1153 | EmployeeAge |
| demotable1297 | EmployeeAge |
| demotable1303 | EmployeeAge |
| demotable1328 | EmployeeAge |
| demotable1378 | EmployeeAge |
| demotable1530 | EmployeeAge |
| demotable1559 | EmployeeAge |
| demotable1586 | EmployeeAge |
| demotable1798 | EmployeeAge |
| demotable1901 | EmployeeAge |
| demotable511  | EmployeeAge |
| demotable912  | EmployeeAge |
+---------------+-------------+
12 rows in set (0.00 sec)

조회 결과, 총 12개의 테이블에 EmployeeAge 컬럼이 존재하는 것을 확인할 수 있습니다.

결과 검증하기

조회 결과가 정확한지 검증해 보겠습니다. 목록에 나온 테이블 중 하나의 구조를 DESC 명령어로 확인합니다.

mysql> DESC demotable1153;

실행 결과를 보면 demotable1153 테이블에 실제로 EmployeeAge 컬럼이 존재하는 것을 알 수 있습니다.

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| EmployeeId   | int(11)     | NO   | PRI | NULL    | auto_increment |
| EmployeeName | varchar(40) | YES  | MUL | NULL    |                |
| EmployeeAge  | int(11)     | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

추가 팁

현재 데이터베이스뿐 아니라 서버 전체의 모든 데이터베이스를 대상으로 검색하고 싶다면 WHERE table_schema = SCHEMA() 조건을 제거하거나, 특정 데이터베이스명을 직접 지정하면 됩니다. 예를 들어 table_schema = 'mydb'처럼 작성하면 mydb 데이터베이스만 대상으로 조회됩니다. 또한 LIKE '%Age%' 패턴 매칭을 활용하면 컬럼명 일부만 알고 있을 때도 유연하게 검색할 수 있습니다.