MySQL에서 특정 열 이름을 가진 모든 테이블 조회하는 방법

데이터베이스를 관리하다 보면 특정 열(컬럼) 이름을 포함하고 있는 테이블이 어떤 것들이 있는지 확인해야 하는 경우가 자주 발생합니다. MySQL에서는 INFORMATION_SCHEMA.COLUMNS 뷰와 LIKE 연산자를 조합하여 이러한 정보를 손쉽게 조회할 수 있습니다.

INFORMATION_SCHEMA.COLUMNS란?

INFORMATION_SCHEMA.COLUMNS는 데이터베이스 내 모든 테이블의 열 정보를 담고 있는 시스템 뷰입니다. 여기에는 열 이름(COLUMN_NAME), 테이블 이름(TABLE_NAME), 데이터 타입(DATA_TYPE) 등 다양한 메타데이터가 저장되어 있어, 원하는 조건으로 필터링하여 조회할 수 있습니다.

예제 쿼리

다음은 열 이름에 'ID'가 포함된 모든 테이블을 조회하는 MySQL 쿼리입니다.

mysql> SELECT COLUMN_NAME AS 'ColumnName', TABLE_NAME AS 'Tablename'
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE COLUMN_NAME LIKE '%ID%'
    -> ORDER BY Tablename
    -> LIMIT 10;

실행 결과

+-------------+---------------+
| ColumnName  | Tablename     |
+-------------+---------------+
| id          | arena         |
| id          | arena1        |
| ID          | cars          |
| ID          | COLLATIONS    |
| ID          | copy_cars     |
| COUNTRY_ID  | countries     |
| REGION_ID   | countries     |
| Customer_Id | customers     |
| Customer_Id | customer_view |
| id          | emp           |
+-------------+---------------+
10 rows in set, 0 warnings (0.15 sec)

쿼리 설명

• COLUMN_NAME AS 'ColumnName': 열 이름을 보기 좋게 별칭으로 출력합니다.
• TABLE_NAME AS 'Tablename': 해당 열이 속한 테이블 이름을 출력합니다.
• WHERE COLUMN_NAME LIKE '%ID%': 열 이름에 'ID'가 포함된 경우만 필터링합니다. 와일드카드 '%'를 앞뒤로 붙이면 부분 일치 검색이 가능합니다.
• ORDER BY Tablename: 결과를 테이블 이름 기준으로 정렬합니다.
• LIMIT 10: 출력되는 행 수를 10개로 제한합니다.

활용 팁

정확히 일치하는 열 이름만 찾고 싶다면 LIKE 대신 등호(=)를 사용할 수 있습니다. 또한 특정 데이터베이스로 범위를 좁히려면 WHERE 절에 TABLE_SCHEMA = '데이터베이스명' 조건을 추가하면 됩니다.

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'ID'
  AND TABLE_SCHEMA = 'your_database';

이 방법을 활용하면 대규모 데이터베이스에서도 원하는 열을 사용 중인 테이블을 빠르게 파악할 수 있습니다.