MySQL で指定したカラム名を持つテーブルを検索する

既存のデータベースに対して、カラム名を指定してテーブルを検索したいような時ありませんか？

ここでは、MySQL で指定したカラム名を持つテーブルを検索する方法をご説明します。

指定したカラム名を持つテーブルを検索する

MySQL で指定したカラム名を持つテーブルを検索するは INFORMATION_SCHEMA.COLUMNS テーブルが使えます。

INFORMATION_SCHEMA テーブルは MySQL サーバのデータベース、テーブル、カラムなどの情報が参照できるテーブルです。

INFORMATION_SCHEMA.COLUMNS テーブルを使って、カラム名を指定してテーブルを検索するクエリーは次の通りです。

SELECT 	TABLE_NAME,
		COLUMN_NAME
FROM 	INFORMATION_SCHEMA.COLUMNS 
WHERE 	TABLE_SCHEMA = 'データベース名'
		AND COLUMN_NAME = 'カラム名'
ORDER BY TABLE_NAME,
		 COLUMN_NAME;

例えば、school_db という名前のデータベースの student_id というカラムを持つテーブルを検索したい時は次のようにできます。

SELECT 	TABLE_NAME,
		COLUMN_NAME
FROM 	INFORMATION_SCHEMA.COLUMNS 
WHERE 	TABLE_SCHEMA = 'school_db'
		AND COLUMN_NAME = 'student_id'
ORDER BY TABLE_NAME,
		 COLUMN_NAME;

[実行結果]

school_db データベースの exam_results テーブルと students テーブルに student_id カラムがありました。

もちろん LIKE を使って、以下のようにカラム名の一部を指定して検索することも可能です。

SELECT 	TABLE_NAME,
		COLUMN_NAME
FROM 	INFORMATION_SCHEMA.COLUMNS 
WHERE 	TABLE_SCHEMA = 'データベース名'
		AND COLUMN_NAME LIKE '%カラム名の一部%'
ORDER BY TABLE_NAME,
		 COLUMN_NAME;

例えば、school_db データベースに name が含まれるカラム名を持つテーブルを検索したい時は次のようにできます。

SELECT 	TABLE_NAME,
		COLUMN_NAME
FROM 	INFORMATION_SCHEMA.COLUMNS 
WHERE 	TABLE_SCHEMA = 'school_db'
		AND COLUMN_NAME LIKE '%name%'
ORDER BY TABLE_NAME,
		 COLUMN_NAME;

[実行結果]

school_db データベースの name が含まれるカラム名を持つテーブルが取得できています。

テーブル名だけが取得したい場合は以下のように SELECT DISTINCT TABLE_NAME とすることで取得できます。

SELECT 	DISTINCT TABLE_NAME
FROM 	INFORMATION_SCHEMA.COLUMNS 
WHERE 	TABLE_SCHEMA = 'school_db'
		AND COLUMN_NAME LIKE '%name%'
ORDER BY TABLE_NAME;

[実行結果]

以上、MySQL で指定したカラム名を持つテーブルを検索する方法をご説明しました。