MySQL 데이터베이스에서 특정 컬럼을 포함한 모든 테이블 조회하기 – 예시: xyz

데이터베이스 작업을 하다 보면 특정 컬럼(열)을 포함하고 있는 테이블이 어떤 것들인지 확인해야 하는 경우가 종종 발생합니다. 예를 들어 'web'이라는 데이터베이스가 있고, 그 안에서 'StudentFirstName'이라는 특정 컬럼을 가진 모든 테이블을 찾아야 한다고 가정해 보겠습니다.

information_schema를 활용한 조회 방법

MySQL에서는 information_schema.columns 뷰를 활용하면 이 문제를 손쉽게 해결할 수 있습니다. 아래 쿼리를 실행하면 해당 컬럼을 포함한 모든 테이블 목록을 얻을 수 있습니다.

mysql> select myColumnName.table_name from
information_schema.columns myColumnName
where myColumnName.column_name = 'StudentFirstName' and table_schema='web';

실행 결과

+---------------+
| TABLE_NAME    |
+---------------+
| demotable109  |
| demotable297  |
| demotable335  |
| demotable395  |
| demotable418  |
| demotable425  |
| demotable436  |
+---------------+
7 rows in set (0.14 sec)

위 결과에서 확인할 수 있듯이, 총 7개의 테이블이 'StudentFirstName'이라는 컬럼명을 포함하고 있습니다.

조회 결과 검증하기

이제 조회된 테이블 중 하나의 구조를 직접 확인하여 실제로 해당 컬럼이 존재하는지 검증해 보겠습니다.

mysql> desc demotable297;

실행 결과에 'StudentFirstName' 컬럼이 포함되어 있는 것을 확인할 수 있습니다.

+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| StudentId        | int(11)      | YES  |     | NULL    |       |
| StudentFirstName | varchar(100) | YES  |     | NULL    |       |
| StudentLastName  | varchar(100) | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

핵심 정리

이 방법의 핵심은 MySQL의 메타데이터 저장소인 information_schema를 활용하는 것입니다. WHERE 절의 table_schema 조건에 데이터베이스 이름을 지정하면, 해당 데이터베이스 내에서 특정 컬럼명을 사용하는 모든 테이블을 빠르게 찾아낼 수 있습니다. 대규모 데이터베이스에서 스키마 분석, 리팩토링 또는 마이그레이션 작업을 진행할 때 매우 유용하게 활용되는 기법이니 꼭 기억해 두시기 바랍니다.