この記事の要点

information_schema は SQL 標準のメタデータビュー、ほぼ全 DB で利用可 主要ビュー: TABLES / COLUMNS / KEY_COLUMN_USAGE / TABLE_CONSTRAINTS / STATISTICS MySQL の SHOW TABLES / DESC tbl は簡易、PostgreSQL の \dt / \d は psql ショートカット PostgreSQL は pg_catalog 系、Oracle は USER_TABLES / ALL_TAB_COLUMNS、SQL Server は sys.tables マイグレーション差分検出 / リバースエンジニアリングでメタデータ参照が必須

メタデータとは

データベースの「データそのもの」ではなく「データ構造の情報」がメタデータです。テーブル一覧・カラム定義・インデックス・制約・外部キーなどを SQL で取得できます。スキーマ可視化・差分検出・マイグレーション生成などで頻繁に使われます。

information_schema (標準 SQL)

information_schema は ANSI/ISO SQL 標準で定義されており、MySQL / PostgreSQL / SQL Server / MariaDB で利用できます (Oracle は提供せず独自カタログ)。

ビュー	内容
SCHEMATA	データベース (スキーマ) 一覧
TABLES	テーブル・ビュー一覧
COLUMNS	カラム情報 (型/NULL可否/デフォルト)
STATISTICS	インデックス情報
KEY_COLUMN_USAGE	キー (PK/UK/FK) を構成するカラム
TABLE_CONSTRAINTS	テーブルの制約 (PK/UK/FK/CHECK)
REFERENTIAL_CONSTRAINTS	外部キー詳細 (参照テーブル等)
VIEWS	ビュー定義
ROUTINES	ストアドプロシージャ・関数
TRIGGERS	トリガー

MySQL でのメタデータ取得

-- データベース一覧
SHOW DATABASES;
SELECT schema_name FROM information_schema.schemata;

-- テーブル一覧
SHOW TABLES;
SHOW TABLES FROM mydb;
SELECT table_name, table_rows, data_length, create_time
FROM information_schema.tables
WHERE table_schema = 'mydb' AND table_type = 'BASE TABLE'
ORDER BY table_name;

-- テーブル定義
DESC users;
SHOW CREATE TABLE users;

-- カラム情報
SELECT column_name, data_type, is_nullable, column_default,
       character_maximum_length, column_comment
FROM information_schema.columns
WHERE table_schema = 'mydb' AND table_name = 'users'
ORDER BY ordinal_position;

-- インデックス
SHOW INDEX FROM users;
SELECT index_name, column_name, non_unique, seq_in_index
FROM information_schema.statistics
WHERE table_schema = 'mydb' AND table_name = 'users'
ORDER BY index_name, seq_in_index;

-- 外部キー
SELECT
    kcu.constraint_name,
    kcu.column_name,
    kcu.referenced_table_name,
    kcu.referenced_column_name
FROM information_schema.key_column_usage kcu
WHERE kcu.table_schema = 'mydb'
  AND kcu.table_name = 'orders'
  AND kcu.referenced_table_name IS NOT NULL;

-- テーブルサイズ TOP 10
SELECT table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY size_mb DESC LIMIT 10;

PostgreSQL でのメタデータ取得

PostgreSQL は information_schema に加えて、より詳細な情報を持つ pg_catalog 系システムカタログを提供します。psql の \d 系コマンドはこれらをラップしています。

-- psql ショートカット (対話シェル限定)
\l            -- データベース一覧
\dn           -- スキーマ一覧
\dt           -- テーブル一覧
\dt+          -- サイズ・コメント付き
\d users      -- テーブル定義 (カラム/インデックス/制約)
\d+ users     -- 詳細版
\di           -- インデックス一覧
\dv           -- ビュー一覧
\df           -- 関数一覧
\du           -- ユーザー (ロール) 一覧

-- SQL での取得 (psql 不要)
-- テーブル一覧
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

-- カラム情報
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'users'
ORDER BY ordinal_position;

-- pg_catalog でテーブルサイズ
SELECT
    schemaname, tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

-- インデックス
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'users';

Oracle でのメタデータ取得

Oracle は information_schema を提供せず、独自のデータディクショナリを使います。プレフィックスで参照可能範囲が変わります:

プレフィックス	意味
USER_*	自分が所有するオブジェクト
ALL_*	自分がアクセス可能な全オブジェクト
DBA_*	DB 内すべて (DBA 権限必要)

-- テーブル一覧
SELECT table_name FROM user_tables ORDER BY table_name;
SELECT owner, table_name FROM all_tables WHERE owner = 'SCOTT';

-- カラム情報
SELECT column_name, data_type, data_length, nullable, data_default
FROM user_tab_columns
WHERE table_name = 'USERS'
ORDER BY column_id;

-- 制約 (PK/FK/CHECK)
SELECT constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = 'USERS';
-- constraint_type:
--   P = Primary Key
--   U = Unique
--   R = Foreign Key (References)
--   C = Check

-- インデックス
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = 'USERS'
ORDER BY index_name, column_position;

-- DDL を取得
SELECT DBMS_METADATA.GET_DDL('TABLE', 'USERS') FROM DUAL;

-- 簡易確認 (sqlplus)
DESC users;

SQL Server でのメタデータ取得

-- sys.* 系 (SQL Server 独自・最も詳細)
SELECT name, create_date, modify_date FROM sys.tables;

SELECT
    t.name AS table_name,
    c.name AS column_name,
    ty.name AS data_type,
    c.is_nullable,
    c.max_length
FROM sys.tables t
INNER JOIN sys.columns c ON c.object_id = t.object_id
INNER JOIN sys.types ty ON ty.user_type_id = c.user_type_id
WHERE t.name = 'users'
ORDER BY c.column_id;

-- information_schema (互換)
SELECT table_name FROM information_schema.tables;

-- ストアドプロシージャでの簡易取得
EXEC sp_help 'users';
EXEC sp_columns 'users';
EXEC sp_helpindex 'users';

-- テーブルサイズ
EXEC sp_spaceused 'users';

実用例: テーブル一覧 + 行数 + サイズ

-- MySQL
SELECT
    table_name,
    table_rows                                   AS approx_rows,
    ROUND(data_length / 1024 / 1024, 2)          AS data_mb,
    ROUND(index_length / 1024 / 1024, 2)         AS index_mb,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
    create_time
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_type = 'BASE TABLE'
ORDER BY (data_length + index_length) DESC;

FAQ

Q: information_schema は重い？
A: テーブル数が多い (数万) と遅くなることがあります。MySQL では innodb_stats_on_metadata=OFF で改善する場合あり。

Q: Oracle の USER_TABLES.NUM_ROWS が正確じゃない
A: DBMS_STATS.GATHER_TABLE_STATS で統計情報を更新する必要があります。MySQL の TABLE_ROWS も InnoDB は概算値です。

Q: 全 DB 共通でカラム情報を取りたい
A: information_schema.COLUMNS が MySQL/PostgreSQL/SQL Server で共通。Oracle だけ別途 ALL_TAB_COLUMNS をクエリ。