データベースにテーブルが増えてくると、どんなテーブルが存在するのか全ての名前を覚えておくことは困難です。
もちろんデータベースには、テーブルの一覧情報を取得して確認するためのSQLコマンドが存在します。
本記事では、MySQLでテーブルの一覧情報を確認するためのSQLコマンドの使い方についてご紹介していきます。
使用中のデータベースに存在するテーブル一覧を表示するSQL
まずは現在使用中のデータベースに存在するテーブル一覧を取得するSQLについてご紹介していきたいと思います。
テーブル一覧を取得するには「SHOW」コマンドを利用します。
基本構文
最初に最もシンプルなテーブル一覧の表示をSHOWコマンドで実施してみましょう。
SHOW TABLES;
今回筆者の環境では「test_db」というデータベースに3つのテストテーブルと1つのVIEWテーブルを作成したため、下記のような結果が表示されます。
+-------------------+ | Tables_in_test_db | +-------------------+ | animal | | animal_name | | product | | user | +-------------------+
テーブルの種類までを表示する
次にSHOWコマンドのオプションである「FULL」を付けて実施してみましょう。
FULLを付けた場合にはテーブルの種類が「BASE TABLE」「VIEW」「SYSTEM VIEW」のいずれかで表示されます。
SHOW FULL TABLES;
実行した結果が下記の通りです。
+-------------------+------------+ | Tables_in_test_db | Table_type | +-------------------+------------+ | animal | BASE TABLE | | animal_name | VIEW | | product | BASE TABLE | | user | BASE TABLE | +-------------------+------------+
条件を絞って表示する
テーブルの表示条件として、データを取得するSQLと同じように「WHERE」や「LIKE」を利用することも可能です。
SHOW TABLES WHERE(LIKE) 条件式;
サンプルでは「product」テーブルを抽出するならば下記のような記述となります。
SHOW TABLES WHERE Tables_in_test_db = "product";
実行した結果が下記の通りです。
+-------------------+ | Tables_in_test_db | +-------------------+ | product | +-------------------+
Tables_in_test_dbについては、作成したデータベース名により異なります。
今回のサンプルでは、test_dbという名前のデータベースを作成して実施しています。
同様に「animal」から始まる名前のテーブルも抽出してみましょう。
SHOW TABLES LIKE 'animal%';
実行した結果が下記の通りです。
+-----------------------------+ | Tables_in_test_db (animal%) | +-----------------------------+ | animal | | animal_name | +-----------------------------+
データベースを指定してテーブル一覧を取得するSQL
次にデータベースを指定してテーブル一覧を取得する方法についても合わせてご紹介しておきます。
上述した「SHOW」コマンドはデータベースを選択した状態で実行しないとエラーとなってしまいます。
一方で、こちらでご紹介するSQLは、SQL文中でデータベースを指定するため、データベースを切り替える必要がありません。
今回はMySQLにログインしたばかりのデータベース未選択状態でSQLを実行していきたいと思います。
SELECT DATABASE();
上記は、選択中のデータベースを確認するコマンドで、実行すると下記のように未選択状態が確認出来ます。
+------------+ | DATABASE() | +------------+ | NULL | +------------+
基本構文
データを選択する際のSELECT文と同じ記述方法ですが、取得する場所を「information_schema.tables」と指定します。
SELECT カラム名(, カラム名, ...) FROM information_schema.tables (WHERE table_schema='データベース名')
今回は「test_db」というデータベースを作成していますので、実際に上述した「SHOW」コマンドと同じようにテーブル一覧を取得してみましょう。
SELECT table_name, table_type FROM information_schema.tables WHERE table_schema='test_db';
実行した結果が下記の通りです。
+-------------+------------+ | TABLE_NAME | TABLE_TYPE | +-------------+------------+ | animal | BASE TABLE | | animal_name | VIEW | | product | BASE TABLE | | user | BASE TABLE | +-------------+------------+
もちろんWHERE句で対象データベースを指定していますので、複数データベースのテーブルを同時に取得することも可能です。
SELECT table_name, table_type FROM information_schema.tables WHERE table_schema='test_db' or table_schema='sample_db';
サンプルのSQLでは別途作成した「sample_db」に作成されているテーブル「test_db」に存在するテーブルを合わせて取得しています。
+-------------+------------+ | TABLE_NAME | TABLE_TYPE | +-------------+------------+ | sample1 | BASE TABLE | | sample2 | BASE TABLE | | animal | BASE TABLE | | animal_name | VIEW | | product | BASE TABLE | | user | BASE TABLE | +-------------+------------+
さいごに：SQLでテーブル一覧を取得しよう
本記事では、SQLでデータベースのテーブル一覧を取得する方法についてご紹介してきました。
一般的に、information_schema.tableからテーブル一覧を取得する代替として「SHOW」コマンドを利用します。
基本は「SHOW」コマンドで事足りることが多いのですが、別データベースのテーブル一覧を取得する方法についても知識として蓄えておきましょう。
今回はMySQLを利用したSQLコマンドをご紹介していきます。
データベース毎に利用可能なコマンドは異なりますので、ご利用のデータベースに合わせて適宜読み替えてください。