MySQL テーブル一覧取得 全カラム情報も取得するには？

MySQLのテーブル一覧取得方法について、サンプルSQLを紹介しながらまとめています。

以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。

MySQLのテーブル一覧取得方法

MySQLでテーブル一覧を取得するには、showステートメントを使用する方法と、MySQLの管理用データベースinformation_schemaテーブルに直接selectクエリを実行する方法があります。

SHOW TABLESでテーブル一覧取得

show tablesの基本構文は以下の通りです。

show tables from データベース名;

likeやwhereを使って条件指定も可能です。

参考）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.5.38 SHOW TABLES 構文

show tables from kabuking_practice;

実行結果は以下のようになります。

実行結果

select文でテーブル一覧取得

MySQLの管理データベースinformation_schemaに直接アクセスしてselect文で情報を取得することも可能です。

showステートメント利用と比較したメリットは、フィルタリングやソート、連結などSQLで可能な処理が何でも使える点です。

なお、MySQLでのinformation_schemaテーブル構造の実装は、ANSi/ISO SQL:2003 標準パート11のSchemataに準拠しています。

select * from information_schema.tables
where table_schema = 'kabuking_practice'

上記のSQLは、information_schemaデータベースのテーブル情報を格納するtablesから、データベース名が「kabuking_practice」の情報を抽出します。

※「kabuking_practice」の部分は、お使いの環境のデータベース名に合わせて変更してください。

実行すると、こうなります。

テーブル一覧と付加情報を取得する方法

select文を使ってテーブル一覧と同時に様々な付加情報を取得することが可能です。

テーブル一覧とデータ件数取得

information_schema.tablesのtable_rowsは、テーブルごとのデータ件数の概算値を格納しています。

以下のSQLは、データベースkabuking_practiceのテーブルと件数の概算値を取得します。

SELECT table_name, table_rows 
FROM information_schema.tables
WHERE table_schema = 'kabuking_practice'

正確な件数が必要な場合は、select count(*) from テーブル名を実行してください。

なお、information_schema内のテーブルの場合、table_rowsはNULLになっています。また、VIEWの場合もNULLになります。

実行すると、こうなります。

実行結果

テーブル一覧とサイズ情報を取得

informatin_schema_tablesのdata_length（データサイズ）とindex_length（インデックスサイズ）から、テーブル全体のサイズ、データ部分のサイズ、インデックス部分のサイズを取得します。単位はメガバイトです。

SELECT 
	table_name,  
	floor((data_length + index_length)/ 1024 / 1024) AS 'total(MB)', 
	floor((data_length)/ 1024 / 1024) AS data_size, 
	floor((index_length)/ 1024 / 1024) AS index_size 
FROM 
	information_schema.tables 
WHERE 
	table_schema = 'kabuking_practice'

実行するとこうなります。

実行結果

※VIEWはサイズ情報がNULLになります。

テーブル一覧とコメント取得

テーブルにコメントを指定している場合、テーブル名とコメントの一覧を取得します。

SELECT table_name, table_comment 
FROM information_schema.tables
WHERE table_schema = 'kabuking_practice'

実行するとこうなります。

実行結果

※VIEWにはコメントが「VIEW」となります。salariesテーブルのみコメントを付加しました。

テーブル一覧とカラム一覧取得

SELECT 
	`TABLE_CATALOG`, 
	`TABLE_SCHEMA`, 
	`TABLE_NAME`, 
	`COLUMN_NAME`, 
	`ORDINAL_POSITION`, 
	`COLUMN_DEFAULT`, 
	`IS_NULLABLE`, 
	`DATA_TYPE`, 
	`CHARACTER_MAXIMUM_LENGTH`, 
	`CHARACTER_OCTET_LENGTH`, 
	`NUMERIC_PRECISION`, 
	`NUMERIC_SCALE`, 
	`DATETIME_PRECISION`, 
	`CHARACTER_SET_NAME`, 
	`COLLATION_NAME`, 
	`COLUMN_TYPE`, 
	`COLUMN_KEY`, 
	`EXTRA`, 
	`PRIVILEGES`, 
	`COLUMN_COMMENT`, 
	`GENERATION_EXPRESSION` 
FROM 
	information_schema.columns 
WHERE 
	table_schema='kabuking_practice'

information_schema.columnsに直接select文を実行することで、カラムの位置、デフォルト値、NULL許可、型などのほか、キー情報やキャラクタセット、カラムのコメントなど様々な情報が取得できます。

また、1クエリで複数のテーブルの情報を取得可能です。

実行するとこうなります。

SHOW columnsでは、一つのテーブルの基本的なカラム情報のみ取得できます。

SHOW columns 
FROM kabuking_practice.employees

実行するとこうなります。

実行結果

テーブル一覧をファイル出力する

テーブル一覧や、その他付加情報をファイル出力するには、以下のようにします。

以下のSQLは、information_schema.tablesテーブルの情報から、table_schemaがkabuking_practiceのレコードを取得して、/tmp/tablelist.dmpに出力します。

SELECT * FROM information_schema.tables
INTO OUTFILE '/tmp/tablelist.dmp'
WHERE table_schema = 'kabuking_practice'

まとめ

ポテパンダの一言メモ

• MySQLのテーブル一覧は、SHOW TABLESで取得できる
• information_schemaに直接select文を実行すると、さらに多くの情報が取得できる
• SQLを工夫することで、データ件数やカラム情報などの付加価値を取得することも可能