MySQL / MariaDBのメタデータを目的から選ぶ

子ページから項目を選択してください。 カラム定義の確認

本記事はMySQL / MariaDBのSQLカテゴリに属する親ページです。カラムの詳細は子ページへ、対話コマンドと定義表示はテーブル定義の確認へ進めます。ほかのDBの既存比較も残しますが、同名のinformation_schemaで全ビュー/列/権限が共通という意味ではありません。

2026-10-26にMySQL8.4.13 / MariaDB11.8.9-ubu2404の新規InnoDB検体で掲載SQLを確認しました。既存本番の権限/設定/構造は変更していません。別製品の旧例や全規模の負荷まで実測したものではありません。

1. 接続先と見える範囲を先に確認する

SELECT VERSION(), DATABASE(), CURRENT_USER();

同じSQLでも接続先・照合アカウント・権限によって見える表/列が異なります。検体では権限のない接続は既存表/列の照会が0件、usersのSELECTだけを持つ接続にはusersだけが見えました。0件を「存在しない」と決めつけたり、一覧を広げるためにroot/全権限を付けたりしません。アカウント名や定義全文を公開資料へ貼る必要もありません。

2. 表とビュー、推定値と実データを分ける

以下は定義確認記事の新規検体を対象にする読取例です。実表名に一律置換せず、schema/対象/権限と負荷を確認します。BASE TABLEに絞ったため検体のviewは含まず、children/parents/usersの3表になりました。

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='cwiki_w28' AND TABLE_TYPE='BASE TABLE'
ORDER BY TABLE_SCHEMA,TABLE_NAME;

TABLE_ROWSやサイズはengine/統計/キャッシュの条件に依存します。InnoDBのTABLE_ROWSを現在の正確な行数と扱わず、COUNTも観測時点・transactionの可視範囲と実行負荷を持つ別の処理です。行数確認のための統計更新・設定変更・全表COUNTを自動追加しません。

SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS AS estimated_rows,
       DATA_LENGTH,INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='cwiki_w28' AND TABLE_TYPE='BASE TABLE'
ORDER BY (DATA_LENGTH+INDEX_LENGTH) DESC,TABLE_SCHEMA,TABLE_NAME
LIMIT 10;

サイズ同値でもschema/表名を最後に並べ、LIMITの対象を不定にしない例です。ただし表示値は表領域や関連ファイルを含む全ディスク使用量や、削除で即回収できる容量とは限りません。DDLやOPTIMIZEを通常の確認作業へ追加しません。

3. 列のKey表示だけで索引や制約を判断しない

SELECT INDEX_NAME,NON_UNIQUE,SEQ_IN_INDEX,COLUMN_NAME,SUB_PART,COLLATION,INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA='cwiki_w28' AND TABLE_NAME='users'
ORDER BY INDEX_NAME,SEQ_IN_INDEX;

索引は名前/一意性だけでなく構成列とSEQ_IN_INDEXを照合します。検体の(email,id)は複合UNIQUEですが、SHOW COLUMNSのemailのKey表示はMULでした。MULだけから「UNIQUEではない」、空欄だけから「どの索引にも属さない」と決めつけません。製品固有の式/長さ/可視性等は対象版の追加項目も調べます。

SELECT CONSTRAINT_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,ORDINAL_POSITION,
       COLUMN_NAME,REFERENCED_TABLE_SCHEMA,REFERENCED_TABLE_NAME,
       REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA='cwiki_w28' AND TABLE_NAME='children'
  AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY CONSTRAINT_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,ORDINAL_POSITION;

複合FKのtenant_id→tenant_id、parent_code→codeを列順と参照schemaまで照合しました。制約名だけで結合したり、参照先の表名だけで関係を決めたりしません。権限で見えないFKを「制約なし」と判断せず、確認を通すための制約解除や行削除はしません。

4. 標準部分と製品固有の拡張を区別する

information_schemaには標準由来の部分がありますが、MySQL/MariaDBのSTATISTICS・COLUMN_TYPE・COLUMN_COMMENT等が全製品にあるとは限りません。Oracleの独自ディクショナリ、PGのpg_catalog、SQL Serverのsys系も、名前だけでは同じ情報になりません。旧比較を出発点に、対象製品/版の公式項目と権限を確認します。metadataからの生成DDLを、そのまま完全な復旧・移行の証拠ともしません。

この記事の要点

information_schema は 標準由来と製品固有の項目が混在するため、対象製品/版の範囲を照合 主要ビュー: 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	MySQL/MariaDBではDB(schema)。他製品のschemaとの違い/可視範囲も確認
TABLES	テーブル・ビュー一覧
COLUMNS	カラム情報 (型/NULL可否/デフォルト)
STATISTICS	MySQL/MariaDBの索引情報。全製品の標準共通ビューではない
KEY_COLUMN_USAGE	キー (PK/UK/FK) を構成するカラム
TABLE_CONSTRAINTS	テーブルの制約 (PK/UK/FK/CHECK)
REFERENTIAL_CONSTRAINTS	外部キー詳細 (参照テーブル等)
VIEWS	ビュー定義
ROUTINES	ストアドプロシージャ・関数
TRIGGERS	トリガー

MySQL でのメタデータ取得

旧例の注意：MySQL用の旧まとめです。対象/権限/列順を絞り、FKの参照schemaや複合順序も確認します。サイズTOP10の同値は不定になり得るため、新例の一意な末尾順序も参照します。

-- データベース一覧
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 系コマンドはこれらをラップしています。

旧例の注意：PGのpsqlメタコマンドとSQLを分けます。サイズ照会の文字列連結は特殊な識別子で失敗し得るため、schema/表名の識別子引用と可視範囲を確認します。

-- 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_*	追加の参照権限が必要な管理用ビュー。接続先と対象範囲を確認

旧例の注意：Oracle用です。USER/ALL/DBAの可視範囲と必要権限を分け、DBA権限の追加を前提にしません。DDLやコメントには内部情報も含まれます。

-- テーブル一覧
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 でのメタデータ取得

旧例の注意：SQL Server用です。同名表をschemaで区別し、sys/INFORMATION_SCHEMAやsp系の表示を完全DDLと同一視しません。

-- 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の統計/サイズ例で、実行中の正確な行数や全ディスク容量ではありません。同値順序/権限/engineとキャッシュの条件も照合します。

-- 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: 対象数/絞込み/権限/キャッシュ/待ちと実行負荷を確認します。特定の件数だけで固定せず、設定変更や統計更新を通常の確認へ自動追加しません。

Q: Oracle の USER_TABLES.NUM_ROWS が正確じゃない
A: NUM_ROWSは統計値で、更新が必ず現在の正確な行数や全接続の状態になる意味ではありません。MySQL InnoDBのTABLE_ROWSも概算です。統計更新を自動対処にせず、必要な観測/負荷/可視範囲を決めます。

Q: 全 DB 共通でカラム情報を取りたい
A: 共通名のCOLUMNSでも項目/型表現/権限/製品固有の範囲が違います。必要な情報を製品別に照合し、Oracleの対象ディクショナリ等も含めて同じ情報を取得できるか確認します。

元のMySQL/MariaDBメタデータ案内

元v2の子ページ選択と書式付きの参照表/SQLも保持します。PROCESSLISTや権限の可視範囲/対象版は別に確認し、セッションSQLや認証情報・ハッシュを公開しません。DB2として記載した関連は別製品のまま参照します。

MySQLのメタデータ関連のSQLに関する記事です。MySQL / MariaDB のスキーマ情報（テーブル定義、カラム情報、インデックス情報など）を取得するためのSQLをまとめています。

子ページから項目を選択してください。

本ページの子ページ

• カラム定義の確認 — テーブルのカラム一覧と型・NULL許容を取得

メタデータの主な参照先

MySQL / MariaDB はメタデータを information_schema という仮想データベースで提供します。SQLでテーブル一覧・カラム情報・権限・統計情報などを取得できます。

ビュー	用途
information_schema.SCHEMATA	データベース（スキーマ）一覧
information_schema.TABLES	テーブル一覧（ビュー含む）
information_schema.COLUMNS	全テーブルのカラム情報
information_schema.STATISTICS	インデックス情報
information_schema.KEY_COLUMN_USAGE	主キー/UNIQUE/外部キーの構成列。schema/表/制約と列順も照合
information_schema.TABLE_CONSTRAINTS	制約一覧
information_schema.PROCESSLIST	実行中セッション
information_schema.USER_PRIVILEGES	可視なグローバル権限の情報。全ロール/表/列権限の一覧ではない

よく使うクエリ集

データベース一覧

旧例の注意：製品/接続/権限と出力列・順序を照合し、同じ見え方とは決めつけません。

SHOW DATABASES; -- 同等のinformation_schemaクエリ SELECT SCHEMA_NAME   FROM information_schema.SCHEMATA;

テーブル一覧

旧例の注意：schemaを限定します。TABLE_ROWS/容量は推定やengine依存であり、実データの完全な状態ではありません。

SHOW TABLES; SHOW TABLES FROM mydb; -- 行数・サイズも見たい場合 SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH   FROM information_schema.TABLES  WHERE TABLE_SCHEMA = 'mydb';

カラム情報

旧例の注意：列順も確認し、COLUMNSだけで制約/索引/全DDLまで分かったと扱いません。

DESC users; SHOW COLUMNS FROM users; SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT   FROM information_schema.COLUMNS  WHERE TABLE_SCHEMA = 'mydb'    AND TABLE_NAME = 'users';

インデックス情報

旧例の注意：索引の構成列/順序/式/可視性を照合します。この旧照会は順序を固定していません。

SHOW INDEX FROM users; SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE   FROM information_schema.STATISTICS  WHERE TABLE_SCHEMA = 'mydb'    AND TABLE_NAME = 'users';

外部キー

旧例の注意：参照先schema/複合列順が不足する旧例です。新しい読取例と対象の制約定義を照合します。

SELECT CONSTRAINT_NAME, COLUMN_NAME,         REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME   FROM information_schema.KEY_COLUMN_USAGE  WHERE TABLE_SCHEMA = 'mydb'    AND TABLE_NAME = 'orders'    AND REFERENCED_TABLE_NAME IS NOT NULL;

CREATE TABLE文の取得

旧例の注意：1表のCREATE表示です。行/trigger/権限/全依存を含む完全backupではありません。

SHOW CREATE TABLE users;

テーブルサイズランキング

旧例の注意：容量は観測値です。同値順序/可視範囲/engine条件を調べ、空き容量の即時回収値と混同しません。

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;

関連

• 親カテゴリ: SQL （MySQL/MariaDB）
• 類似（DB2）: テーブル一覧の確認 / テーブル定義の確認
• 関連: ユーザーおよびパスワード一覧の確認

確認範囲と一次資料

新規の小規模InnoDB検体と限定した読取権限で確認した範囲です。全負荷/全権限/全製品・全GUI/旧dumpの実行・本番復旧/既存アプリの全設定を保証せず、調査のために本番設定/依存/権限を追加していません。

• MySQL8.4 SHOW COLUMNS / SHOW CREATE TABLE / STATISTICS / mysqldump
• MariaDB SHOW COLUMNS / PostgreSQL18 information schema
• Laravel13のDB確認 / Django5.2モデルのmeta / inspectdb / SQLite PRAGMA