MySQL 컬럼명으로 테이블 찾기

MySQL 컬럼 찾기

MySQL 컬럼명으로 테이블 찾기

1 방법

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME='컬럼명';

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%컬럼명%';

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%컬럼명%' AND TABLE_SCHEMA='디비명';

2 실행예시

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE COLUMN_NAME LIKE '%table_priv%';
+--------------+-------------+-----------------------+
| TABLE_SCHEMA | TABLE_NAME  | COLUMN_NAME           |
+--------------+-------------+-----------------------+
| mysql        | db          | Create_tmp_table_priv |
| mysql        | host        | Create_tmp_table_priv |
| mysql        | tables_priv | Table_priv            |
| mysql        | user        | Create_tmp_table_priv |
+--------------+-------------+-----------------------+
4 rows in set (0.07 sec)

3 같이 보기

• 오라클 컬럼명으로 테이블 찾기

4 참고

• http://stackoverflow.com/questions/193780/how-to-find-all-the-tables-in-mysql-with-specific-column-names-in-them