"MySQL 컬럼명으로 테이블 찾기"의 두 판 사이의 차이

2017년 6월 27일 (화) 11:35 기준 최신판

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

@@ 4번째 줄: / 4번째 줄: @@
 ==방법==
 <source lang='sql'>
-SELECT TABLE_NAME, COLUMN_NAME
+SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE COLUMN_NAME='컬럼명';
 </source>
 <source lang='sql'>
-SELECT TABLE_NAME, COLUMN_NAME
+SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE COLUMN_NAME LIKE '%컬럼명%';
+</source>
+<source lang='sql'>
+SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
+FROM INFORMATION_SCHEMA.COLUMNS
+WHERE COLUMN_NAME LIKE '%컬럼명%' AND TABLE_SCHEMA='디비명';
+</source>
+==실행예시==
+<source lang='console'>
+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 |
++--------------+-------------+-----------------------+
+rows in set (0.07 sec)
 </source>
 ==같이 보기==
-*[[오라클 컬러명으로 테이블 찾기]]
+*[[오라클 컬럼명으로 테이블 찾기]]
+==참고==
+*http://stackoverflow.com/questions/193780/how-to-find-all-the-tables-in-mysql-with-specific-column-names-in-them
 [[분류: MySQL]]