SQLテーブル一覧・カラム一覧の取得方法｜DB別早見表つき

テーブル一覧やカラム一覧は、information_schema（インフォメーション・スキーマ）という「テーブルの情報を集めた読み取り専用のビュー群」にSELECTを投げると取得できます。 MySQL・PostgreSQL・SQL Serverはこの方法が共通で使え、OracleはUSER_TABLESなどのデータディクショナリビュー、SQLiteはsqlite_masterとPRAGMAが代わりになります。

この記事では、「テーブル一覧／カラム一覧／主キー・外部キー／インデックス／コメント／CREATE文の出力」の6つの目的を、5つのDBごとに早見表にまとめました。MySQL 8.0.46・PostgreSQL 16.15・SQLite 3.45.1では、同じ構成のテーブルを作って実際に実行した結果を載せています。SQL ServerとOracleのクエリは今回の環境では未実行で、一般に知られたビュー名・構文として掲載しています（該当箇所に明記します）。

6目的×5DBのコピペ用クエリ早見表

SQL ServerとOracleの列は未実行です。orders は対象のテーブル名、mydb はデータベース名に読み替えてください。

この記事で使うテーブル（コピペ用）

会員（members）と注文（orders）の2テーブル、インデックス1つ、ビュー1つ、コメントを用意します。結果を見比べやすいよう、3つのDBで同じ構成にしています。

PostgreSQL用です。

CREATE TABLE members (
  member_id  INT PRIMARY KEY,
  name       VARCHAR(50) NOT NULL,
  email      VARCHAR(100) NOT NULL UNIQUE,
  joined_on  DATE NOT NULL DEFAULT CURRENT_DATE
);
CREATE TABLE orders (
  order_id   INT PRIMARY KEY,
  member_id  INT NOT NULL REFERENCES members (member_id),
  amount     INT NOT NULL,
  ordered_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_orders_member ON orders (member_id);
CREATE VIEW v_member_orders AS
  SELECT m.name, o.amount FROM members m JOIN orders o USING (member_id);
COMMENT ON TABLE members IS '会員';
COMMENT ON COLUMN members.email IS 'ログイン用メールアドレス';
COMMENT ON TABLE orders IS '注文';
COMMENT ON COLUMN orders.amount IS '注文金額（税込）';

MySQLでは、コメントを amount INT NOT NULL COMMENT '注文金額（税込）' や、閉じかっこのあとの COMMENT = '注文' のようにCREATE TABLEの中に書き、外部キーは FOREIGN KEY (member_id) REFERENCES members (member_id) を表の定義に足し、joined_on のデフォルトは DEFAULT (CURRENT_DATE) としました。

SQLiteでは、型を INTEGER と TEXT に置き換え、外部キーは member_id INTEGER NOT NULL REFERENCES members (member_id) と列に直接書き、コメントは -- 注文金額（税込） の形でCREATE文の中に書きました（orders の全文は「CREATE文を出力する」の節に載せています）。

information_schemaとは

information_schema は、標準SQLで決められた「データベースの中にどんなテーブル・カラム・制約があるか」を調べるためのビューの集まりです。ふつうのテーブルと同じようにSELECTでき、WHEREでの絞り込みやJOINも使えます。

DBごとの対応状況です。

「スキーマ」が指すものがDBで違う点も押さえておくと、絞り込みで迷いません。MySQLでは table_schema はデータベース名（DATABASE() で今のDB名を取得）、PostgreSQLでは public などのスキーマ名、SQL Serverでは dbo などのスキーマ名です。

テーブル一覧を取得する

MySQL

SHOW TABLES;

members・orders・v_member_orders の3件が返りました。SHOW TABLES にはビューも含まれます。 SHOW FULL TABLES; にすると Table_type 列が加わり、BASE TABLE と VIEW を見分けられます。

information_schema を使うと、コメントなどの列も一緒に取れます。

SELECT table_name, table_type, table_comment
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY table_name;

members には「会員」、orders には「注文」、ビューの v_member_orders には「VIEW」という文字が table_comment に入っていました。MySQL 8.0では、列名を小文字で書いても結果の見出しは大文字（TABLE_NAME）で返りました。プログラムから列名で値を取り出すときは、AS table_name のように別名を付けておくと扱いやすくなります。

PostgreSQL

SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

members と orders が BASE TABLE、v_member_orders が VIEW として返りました。

psql を使っているなら \dt が手軽です。こちらは members と orders の2件だけが表示され、ビューは含まれませんでした（ビューは \dv）。

SQLで書くなら、pg_tables も使えます。SELECT schemaname, tablename FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema'); で、ユーザーが作ったテーブルだけが返ります。

SQLite

SQLiteには information_schema がありません。実際に投げるとこうなります。

sqlite> SELECT table_name FROM information_schema.tables;
Parse error near line 22: no such table: information_schema.tables

代わりに、sqlite_master（3.33.0以降は sqlite_schema という別名でも可。3.45.1でどちらも動くことを確認）を使います。

SELECT type, name, tbl_name FROM sqlite_master ORDER BY type, name;

index が2行（idx_orders_member と、UNIQUE制約から自動で作られた sqlite_autoindex_members_1）、table が2行（members、orders）、view が1行（v_member_orders）の計5行が返りました。

sqlite_master にはテーブル・インデックス・ビュー・トリガーが1つの表にまとまっているので、type = 'table' で絞るのが基本です。

SELECT name FROM sqlite_master
WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
ORDER BY name;

members と orders の2件が返りました。sqlite3 コマンドの中なら .tables でも一覧できます（ビューも含まれます）。

members          orders           v_member_orders

SQL Server・Oracle（未実行）

-- SQL Server（未実行）
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE';

-- Oracle（未実行）
SELECT table_name FROM user_tables ORDER BY table_name;          -- 自分が所有するテーブル
SELECT owner, table_name FROM all_tables WHERE owner = 'APP';    -- 参照できるテーブル

Oracleのデータディクショナリビューは、接頭辞で範囲が決まります。USER_ は自分が所有するもの、ALL_ は参照できるもの、DBA_ はデータベース全体（権限が必要）です。

Oracleでは、引用符なしで作ったテーブル名・カラム名は大文字で格納されるため、条件は 'ORDERS' のように大文字で書くのが一般的です。

スキーマの絞り込みを忘れるとシステム表が混ざる

information_schema.tables には、DBが内部で使うテーブルやビューも入っています。WHEREで table_schema を絞らないと、それらが一緒に返ってきます。

スキーマごとの件数を数えるクエリを、PostgreSQL 16.15とMySQL 8.0.46で実行しました。

SELECT table_schema, count(*)
FROM information_schema.tables
GROUP BY table_schema
ORDER BY table_schema;

自分で作ったのは3件だけで、PostgreSQLでは209件、MySQLでは328件がシステム側のものです。PostgreSQLで絞り込まずに先頭5件を取ると、_pg_foreign_data_wrappers など information_schema の内部テーブルばかりが並びました。

カラム一覧（information_schema.columns）ではさらに差が開き、絞り込まない場合の件数はPostgreSQLで2,065行、MySQLで3,544行でした（どちらも自作テーブルのカラムは8行、ビューを含めて10行）。

絞り込みの書き方をDB別にまとめます。

SQLiteでも、AUTOINCREMENT 付きのテーブルを作ると sqlite_sequence という内部テーブルが自動で作られ、一覧に現れます（3.45.1で確認）。name NOT LIKE 'sqlite_%' を付けておくと、こうした内部テーブルを除けます。

カラム一覧を取得する

PostgreSQL

SELECT ordinal_position AS no, column_name, data_type,
       character_maximum_length AS len, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'orders'
ORDER BY ordinal_position;

 no | column_name |          data_type          | len | is_nullable |  column_default
----+-------------+-----------------------------+-----+-------------+-------------------
  1 | order_id    | integer                     |     | NO          |
  2 | member_id   | integer                     |     | NO          |
  3 | amount      | integer                     |     | NO          |
  4 | ordered_at  | timestamp without time zone |     | NO          | CURRENT_TIMESTAMP
(4 rows)

ORDER BY ordinal_position を付けると、CREATE TABLEで定義した順に並びます。 付けない場合の順序は決まっていません。

psqlの \d orders なら、カラムの下に Indexes（orders_pkey、idx_orders_member）と Foreign-key constraints（orders_member_id_fkey）も一度に表示されます。

MySQL

SELECT ordinal_position AS no, column_name, column_type, is_nullable,
       column_default, column_key, extra, column_comment
FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = 'orders'
ORDER BY ordinal_position;

結果には、amount の行の COLUMN_COMMENT に「注文金額（税込）」、order_id の COLUMN_KEY に PRI、member_id に MUL が入っていました。MySQLの information_schema.columns には、標準の列に加えて column_type（varchar(50) のような長さつきの型）、column_key、column_comment といった独自の列があります。COLUMN_KEY の読み方は次のとおりです。

手早く見るなら DESC orders;（SHOW COLUMNS FROM orders; と同じ）です。

Field・Type・Null・Key・Default・Extra の6列で表示され、ordered_at の Extra には DEFAULT_GENERATED が入っていました。コメントも見たいときは SHOW FULL COLUMNS FROM orders; を使うと、Collation・Privileges・Comment の列が加わります。

SQLite

PRAGMA table_info(orders);

+-----+------------+---------+---------+-------------------+----+
| cid |    name    |  type   | notnull |    dflt_value     | pk |
+-----+------------+---------+---------+-------------------+----+
| 0   | order_id   | INTEGER | 0       |                   | 1  |
| 1   | member_id  | INTEGER | 1       |                   | 0  |
| 2   | amount     | INTEGER | 1       |                   | 0  |
| 3   | ordered_at | TEXT    | 1       | CURRENT_TIMESTAMP | 0  |
+-----+------------+---------+---------+-------------------+----+

PRAGMAの結果をWHEREで絞ったりJOINしたりしたいときは、関数形式の pragma_table_info('orders') をFROMに書きます（SELECT name, type, "notnull", pk FROM pragma_table_info('orders');）。こちらは普通のSELECTとして扱えます。

結果は PRAGMA table_info(orders) と同じでした。notnull はキーワードと重なるため、列名として使うときは二重引用符で囲みます。

SQL Server・Oracle（未実行）

SQL Serverは共通形の information_schema.columns がそのまま使えます。Oracleは次のビューを使います。

-- Oracle（未実行）
SELECT column_id, column_name, data_type, data_length, nullable, data_default
FROM user_tab_columns
WHERE table_name = 'ORDERS'
ORDER BY column_id;

Oracleの USER_TAB_COLUMNS は information_schema.columns に当たるビューで、並び順の列は column_id、NULL可否の列は nullable（Y / N）です。

主キー・外部キーを確認する

PostgreSQL：table_constraints と key_column_usage を結合する

主キーの一覧です（members は member_id、orders は order_id が返りました）。

SELECT tc.table_name, tc.constraint_name, kcu.column_name, kcu.ordinal_position
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON kcu.constraint_schema = tc.constraint_schema
 AND kcu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'PRIMARY KEY' AND tc.table_schema = 'public'
ORDER BY tc.table_name, kcu.ordinal_position;

外部キーは、参照先を constraint_column_usage から取ります。

SELECT tc.table_name, kcu.column_name,
       ccu.table_name AS ref_table, ccu.column_name AS ref_column,
       tc.constraint_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON kcu.constraint_schema = tc.constraint_schema
 AND kcu.constraint_name = tc.constraint_name
JOIN information_schema.constraint_column_usage ccu
  ON ccu.constraint_schema = tc.constraint_schema
 AND ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema = 'public';

orders.member_id が members.member_id を参照している1行（制約名 orders_member_id_fkey）が返りました。

複数カラムの外部キーでは、この結合だと参照元と参照先の組み合わせが掛け合わさって返ります。複合キーを正確に対応づけたいときは、psqlの \d テーブル名 の表示や、pg_constraint と pg_get_constraintdef() を使うと定義がそのまま読めます。

MySQL：key_column_usage だけで参照先まで取れる

MySQLの key_column_usage には、参照先を表す referenced_table_name と referenced_column_name の列があります。

SELECT table_name, constraint_name, column_name,
       referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema = DATABASE()
ORDER BY table_name, constraint_name, ordinal_position;

+------------+-----------------+-------------+-----------------------+------------------------+
| TABLE_NAME | CONSTRAINT_NAME | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-----------------+-------------+-----------------------+------------------------+
| members    | email           | email       | NULL                  | NULL                   |
| members    | PRIMARY         | member_id   | NULL                  | NULL                   |
| orders     | orders_ibfk_1   | member_id   | members               | member_id              |
| orders     | PRIMARY         | order_id    | NULL                  | NULL                   |
+------------+-----------------+-------------+-----------------------+------------------------+

SQLite

主キーは PRAGMA table_info の pk 列で、外部キーは次のPRAGMAで確認します。

PRAGMA foreign_key_list(orders);

結果は1行で、table が members、from が member_id、to が member_id でした。from が自分のカラム、table と to が参照先のテーブルとカラムです。

SQL Server・Oracle（未実行）

SQL Serverは、PostgreSQLの節と同じ table_constraints と key_column_usage の結合が使えます。Oracleは次のとおりです。

-- Oracle（未実行）: constraint_type が P = 主キー、R = 外部キー
SELECT c.table_name, c.constraint_type, cc.column_name
FROM user_constraints c
JOIN user_cons_columns cc ON cc.constraint_name = c.constraint_name
WHERE c.constraint_type IN ('P', 'R');

インデックスを確認する

インデックスは標準の information_schema には定義がなく、DBごとの手段を使います。

PostgreSQL

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

orders の2行を抜粋します（全体では members の2行を含む4行が返りました）。

 orders    | idx_orders_member | CREATE INDEX idx_orders_member ON public.orders USING btree (member_id)
 orders    | orders_pkey       | CREATE UNIQUE INDEX orders_pkey ON public.orders USING btree (order_id)

indexdef にはインデックスを作り直せるCREATE文がそのまま入っています。主キーとUNIQUE制約のために自動で作られたインデックスも一覧に含まれます。

MySQL

MySQLは information_schema.statistics にインデックスの情報を持っています。

SELECT table_name, index_name, non_unique, seq_in_index, column_name
FROM information_schema.statistics
WHERE table_schema = DATABASE()
ORDER BY table_name, index_name, seq_in_index;

members の email と PRIMARY、orders の idx_orders_member と PRIMARY の4行が返りました。non_unique が0ならユニーク、1なら重複可です。seq_in_index は複合インデックスの中での順番です。1テーブルだけ見るなら SHOW INDEX FROM orders; でも同じ情報（Key_name、Seq_in_index、Column_name、Index_type など）が表示されます。

SQLite

PRAGMA index_list(orders);
PRAGMA index_info(idx_orders_member);

orders では idx_orders_member（unique が0、origin が c）、index_info では member_id が返りました。origin は、c が CREATE INDEX で作ったもの、u が UNIQUE制約由来、pk が主キー由来です。members で実行すると、UNIQUE制約から自動で作られた sqlite_autoindex_members_1（origin が u）が表示されました。

SQL Server・Oracle（未実行）

SQL Serverは EXEC sp_helpindex 'orders';、または sys.indexes と sys.index_columns の結合で確認します。Oracleは user_indexes（uniqueness 列）と user_ind_columns（column_name、column_position 列）を index_name で結合します。

テーブル・カラムのコメントを取得する

PostgreSQL

PostgreSQLのコメントは information_schema には入っておらず、obj_description() と col_description() という関数で取り出します。

-- テーブルのコメント
SELECT c.relname AS table_name, obj_description(c.oid, 'pg_class') AS table_comment
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'r'
ORDER BY 1;

members に「会員」、orders に「注文」が返りました。

-- カラムのコメント
SELECT cols.table_name, cols.column_name,
       col_description(format('%I.%I', cols.table_schema, cols.table_name)::regclass,
                       cols.ordinal_position) AS column_comment
FROM information_schema.columns cols
WHERE cols.table_schema = 'public' AND cols.table_name = 'orders'
ORDER BY cols.ordinal_position;

amount の行にだけ「注文金額（税込）」が入り、コメントのない列は空で返りました。

psqlなら \d+ members の Description 列にカラムのコメントが表示されます。

MySQL

MySQLは information_schema の中にコメントの列があります。テーブルは tables.table_comment、カラムは columns.column_comment です（クエリは「テーブル一覧」「カラム一覧」の節に載せたとおりです）。

SQLite

SQLiteにはコメントを登録する機能がありません。ただし、CREATE文の中に書いた -- のコメントは、sqlite_master の sql 列にそのまま残ります（次の節の出力で確認できます）。カラムの説明を残したいときは、CREATE文の中に書いておくと後から読めます。

SQL Server・Oracle（未実行）

Oracleは、テーブルのコメントを user_tab_comments、カラムのコメントを user_col_comments の comments 列から取得します。

SQL Serverでは、説明を拡張プロパティ（MS_Description）に入れる慣習があり、sys.extended_properties から取り出します。

CREATE文（DDL）を出力する

MySQL

SHOW CREATE TABLE orders\G

*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `order_id` int NOT NULL,
  `member_id` int NOT NULL,
  `amount` int NOT NULL COMMENT '注文金額（税込）',
  `ordered_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`order_id`),
  KEY `idx_orders_member` (`member_id`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `members` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='注文'

あとから CREATE INDEX で足したインデックスや、自動で付いた外部キー名（orders_ibfk_1）、ストレージエンジン、文字コードまで1つのCREATE文にまとまって出力されます。末尾の \G は mysql クライアントで結果を縦に表示する指定です。

PostgreSQL

PostgreSQLにはSHOW CREATE TABLEに当たるSQLがないため、シェルから pg_dump を使います。-s（--schema-only）で定義だけ、-t でテーブルを指定します。

pg_dump -d t221 --schema-only --no-owner -t orders

出力からコメント行とSET文を除いた部分です（pg_dump 16.15）。

CREATE TABLE public.orders (
    order_id integer NOT NULL,
    member_id integer NOT NULL,
    amount integer NOT NULL,
    ordered_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);
COMMENT ON TABLE public.orders IS '注文';
COMMENT ON COLUMN public.orders.amount IS '注文金額（税込）';
ALTER TABLE ONLY public.orders
    ADD CONSTRAINT orders_pkey PRIMARY KEY (order_id);
CREATE INDEX idx_orders_member ON public.orders USING btree (member_id);
ALTER TABLE ONLY public.orders
    ADD CONSTRAINT orders_member_id_fkey FOREIGN KEY (member_id) REFERENCES public.members(member_id);

主キー・インデックス・外部キー・コメントは、CREATE TABLEとは別の文として出力されます。

SQLite

SELECT sql FROM sqlite_master WHERE name = 'orders';

SQLiteは、テーブルを作ったときのCREATE文をそのまま文字列として保存しています。 改行や -- コメントも入力したとおりに残ります。sqlite3 コマンドの .schema orders なら、同じCREATE文に加えて、関連するインデックスのCREATE文も表示されます。

CREATE TABLE orders (
  order_id   INTEGER PRIMARY KEY,
  member_id  INTEGER NOT NULL REFERENCES members (member_id),
  amount     INTEGER NOT NULL, -- 注文金額（税込）
  ordered_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_orders_member ON orders (member_id);

SQL Server・Oracle（未実行）

Oracleは SELECT DBMS_METADATA.GET_DDL('TABLE', 'ORDERS') FROM dual; でCREATE文を文字列として取得する方法が広く使われています。SQL ServerにはCREATE文を返す標準のSQLがなく、管理ツールのスクリプト生成機能を使うのが一般的です。いずれも今回は未実行です。

特定のカラム名を持つテーブルを逆引きする

「member_id というカラムを持つテーブルはどれか」のように、カラム名からテーブルを探す使い方です。カラムの型をそろえる作業や、影響範囲の調査に役立ちます。

-- PostgreSQL
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE column_name = 'member_id'
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_name;

members と orders の2行が返りました。

-- MySQL
SELECT table_name, column_name, column_type
FROM information_schema.columns
WHERE column_name = 'member_id' AND table_schema = DATABASE()
ORDER BY table_name;

MySQLでも members と orders の2行が返りました。SQLiteでは、sqlite_master の各テーブルに pragma_table_info を掛け合わせます。

-- SQLite
SELECT m.name AS table_name, p.name AS column_name, p.type
FROM sqlite_master m
JOIN pragma_table_info(m.name) p
WHERE m.type = 'table' AND p.name = 'member_id'
ORDER BY m.name;

SQLite 3.45.1でも同じ2行が返りました。

「_id で終わるカラム」のようにあいまいに探すときは column_name LIKE '%\_id' ESCAPE '\' のように書きます。LIKEの中の _ は「任意の1文字」を表すため、文字としての下線を探すときはエスケープします。Oracleでは SELECT table_name FROM user_tab_columns WHERE column_name = 'MEMBER_ID'; が同じ役割です（未実行）。

定義書用に全テーブルのカラム情報を一括出力する

テーブル定義書のもとになる一覧を、1本のクエリで出力します。結果をCSVに書き出せば、そのまま表計算ソフトで開けます。

PostgreSQL

SELECT c.table_name, c.ordinal_position AS no, c.column_name,
       c.data_type || COALESCE('(' || c.character_maximum_length || ')', '') AS type,
       c.is_nullable AS nullable, c.column_default AS default_value,
       CASE WHEN pk.column_name IS NOT NULL THEN 'PK' ELSE '' END AS key,
       col_description(format('%I.%I', c.table_schema, c.table_name)::regclass,
                       c.ordinal_position) AS comment
FROM information_schema.columns c
JOIN information_schema.tables t
  ON t.table_schema = c.table_schema AND t.table_name = c.table_name
 AND t.table_type = 'BASE TABLE'
LEFT JOIN (
  SELECT kcu.table_schema, kcu.table_name, kcu.column_name
  FROM information_schema.table_constraints tc
  JOIN information_schema.key_column_usage kcu
    ON kcu.constraint_schema = tc.constraint_schema
   AND kcu.constraint_name = tc.constraint_name
  WHERE tc.constraint_type = 'PRIMARY KEY'
) pk ON pk.table_schema = c.table_schema
    AND pk.table_name = c.table_name
    AND pk.column_name = c.column_name
WHERE c.table_schema = 'public'
ORDER BY c.table_name, c.ordinal_position;

 table_name | no | column_name |            type             | nullable |   default_value   | key |         comment
------------+----+-------------+-----------------------------+----------+-------------------+-----+--------------------------
 members    |  1 | member_id   | integer                     | NO       |                   | PK  |
 members    |  2 | name        | character varying(50)       | NO       |                   |     |
 members    |  3 | email       | character varying(100)      | NO       |                   |     | ログイン用メールアドレス
 members    |  4 | joined_on   | date                        | NO       | CURRENT_DATE      |     |
 orders     |  1 | order_id    | integer                     | NO       |                   | PK  |
 orders     |  2 | member_id   | integer                     | NO       |                   |     |
 orders     |  3 | amount      | integer                     | NO       |                   |     | 注文金額（税込）
 orders     |  4 | ordered_at  | timestamp without time zone | NO       | CURRENT_TIMESTAMP |     |
(8 rows)

information_schema.tables と結合して table_type = 'BASE TABLE' に絞っているのは、ビューのカラムを除くためです。psqlでCSVに書き出すには、クエリを1行にして \copy (SELECT ...) TO 'columns.csv' WITH (FORMAT csv, HEADER) と書くか、psql --csv -f query.sql -o columns.csv のように起動オプションで指定します（この書き出しは今回未実行です）。

MySQL

MySQLは型・キー・コメントが information_schema.columns にそろっているので、クエリが短くなります。

SELECT c.table_name, c.ordinal_position AS no, c.column_name,
       c.column_type AS type, c.is_nullable AS nullable,
       c.column_default AS default_value, c.column_key AS `key`,
       c.column_comment AS comment
FROM information_schema.columns c
JOIN information_schema.tables t
  ON t.table_schema = c.table_schema AND t.table_name = c.table_name
WHERE c.table_schema = DATABASE() AND t.table_type = 'BASE TABLE'
ORDER BY c.table_name, c.ordinal_position;

PostgreSQLと同じ8行が返り、key 列には PRI・UNI・MUL、comment 列にはカラムコメントが入りました。DEFAULT (CURRENT_DATE) と書いた列のデフォルト値は curdate() と表示されました。定義したときの書き方と表示が少し変わる例です。

SQLite

SELECT m.name AS table_name, p.cid + 1 AS no, p.name AS column_name, p.type,
       CASE p."notnull" WHEN 1 THEN 'NO' ELSE 'YES' END AS nullable,
       p.dflt_value AS default_value,
       CASE WHEN p.pk > 0 THEN 'PK' ELSE '' END AS key
FROM sqlite_master m
JOIN pragma_table_info(m.name) p
WHERE m.type = 'table' AND m.name NOT LIKE 'sqlite_%'
ORDER BY m.name, p.cid;

8行が返りました。このクエリでは主キーの行（member_id、order_id）の nullable が YES になります。NOT NULL を明示していない列の notnull が0で返るためです。定義書に載せるときは「PK の列は NO」と読み替えるか、CASEに p.pk > 0 の条件を足します。

つまずきやすい点と直し方

よくある質問

Q. information_schema と、SHOW TABLES や \dt はどちらを使えばよいですか？ A. 画面で手早く確かめるだけなら SHOW TABLES（MySQL）や \dt（psql）が短くて便利です。条件で絞る、ほかの情報と結合する、プログラムから結果を使う、複数のDBで同じSQLを使い回す、といった場合は information_schema が向いています。

Q. Oracleで information_schema.tables を使えますか？ A. Oracleには information_schema が用意されていないため、USER_TABLES、USER_TAB_COLUMNS、USER_CONSTRAINTS などのデータディクショナリビューを使います（今回は未実行）。接頭辞の USER_ を ALL_ や DBA_ に変えると、対象の範囲が広がります。

Q. SQLiteでカラム一覧をSELECT文として取得できますか？ A. できます。SELECT name, type FROM pragma_table_info('orders'); のように、PRAGMAを関数形式でFROMに書くと、WHEREでの絞り込みや sqlite_master とのJOINが使えます。3.45.1で動作を確認しました。

Q. テーブルの行数も一緒に取れますか？ A. MySQLの information_schema.tables には table_rows という列がありますが、InnoDBでは概算値です。正確な件数が必要なときは、テーブルごとに SELECT COUNT(*) を実行します。

まとめ

• MySQL・PostgreSQL・SQL Serverは information_schema.tables / columns にSELECTすれば、テーブル一覧とカラム一覧が取れる
• Oracleは USER_TABLES / USER_TAB_COLUMNS、SQLiteは sqlite_master / PRAGMA table_info が代わりになる
• table_schema を絞らないとシステム側のテーブルが混ざる。今回の環境では、自作3件に対してPostgreSQLで209件、MySQLで328件が一緒に返った
• 主キー・外部キーは table_constraints と key_column_usage。MySQLは key_column_usage だけで参照先まで取れる
• インデックスとコメントはDBごとの手段（pg_indexes・col_description()、information_schema.statistics・column_comment、PRAGMA index_list）を使う
• CREATE文の出力は SHOW CREATE TABLE（MySQL）、pg_dump -s -t（PostgreSQL）、.schema（SQLite）
• カラム名からの逆引きと定義書用の一括出力は、information_schema.columns を起点にすると1本のクエリで書ける

株式会社RJCでは、テーブル定義の調べ方やinformation_schemaの使い方を仲間と学び合っています。こうした知識を共有しながら、日々技術を磨いています。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。