【SQL】テーブル一覧を取得する方法｜MySQL・PostgreSQL・Oracle・SQL Server・SQLite対応

SHOW TABLES;

SHOW FULL TABLES;

-- 別のデータベースのテーブルを表示
SHOW TABLES FROM other_database;

-- IN でも同じ結果
SHOW TABLES IN other_database;

-- 'user' で始まるテーブルのみ
SHOW TABLES LIKE 'user%';

-- '_log' で終わるテーブルのみ
SHOW TABLES LIKE '%_log';

-- 'order' を含むテーブル
SHOW TABLES LIKE '%order%';

-- すべてのテーブルの詳細情報
SHOW TABLE STATUS;

-- 名前・エンジン・行数・更新日時だけ取得
SHOW TABLE STATUS WHERE Engine = 'InnoDB';

-- テーブル一覧
\dt

-- 詳細情報付き（サイズ・説明）
\dt+

-- 特定スキーマのテーブル
\dt myschema.*

-- パターンで絞り込み
\dt user*

SELECT table_name
FROM   information_schema.tables
WHERE  table_schema = 'public'
  AND  table_type   = 'BASE TABLE'
ORDER BY table_name;

SELECT schemaname, tablename, tableowner
FROM   pg_tables
WHERE  schemaname NOT LIKE 'pg_%'
  AND  schemaname != 'information_schema'
ORDER BY tablename;

SELECT table_name
FROM   user_tables
ORDER BY table_name;

-- 所有者別に一覧表示
SELECT owner, table_name
FROM   all_tables
ORDER BY owner, table_name;

-- 特定の表領域に絞り込み
SELECT owner, table_name
FROM   all_tables
WHERE  tablespace_name = 'USERS'
ORDER BY owner, table_name;

-- DBA権限が必要
SELECT owner, table_name, num_rows
FROM   dba_tables
WHERE  owner NOT IN ('SYS', 'SYSTEM', 'OUTLN')
ORDER BY owner, table_name;

-- 基本的なテーブル一覧
SELECT name AS table_name
FROM   sys.tables
ORDER BY name;

-- スキーマ名付きで取得
SELECT s.name AS schema_name,
       t.name AS table_name,
       t.create_date,
       t.modify_date
FROM   sys.tables t
JOIN   sys.schemas s ON t.schema_id = s.schema_id
ORDER BY s.name, t.name;

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME;

SELECT name AS table_name
FROM   sys.objects
WHERE  type = 'U'   -- U = User Table
ORDER BY name;

-- テーブル名の一覧
SELECT name
FROM   sqlite_master
WHERE  type = 'table'
ORDER BY name;

-- CREATE TABLE 文も確認
SELECT name, sql
FROM   sqlite_master
WHERE  type = 'table'
  AND  name NOT LIKE 'sqlite_%'
ORDER BY name;

-- すべてのテーブル
.tables

-- パターンで絞り込み
.tables user%

-- テーブルのスキーマ（CREATE TABLE文）を確認
.schema customers

SELECT table_catalog,    -- データベース名
       table_schema,     -- スキーマ名
       table_name,       -- テーブル名
       table_type        -- BASE TABLE or VIEW
FROM   information_schema.tables
WHERE  table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;

-- information_schema から概算行数を取得
SELECT table_name,
       table_rows
FROM   information_schema.tables
WHERE  table_schema = 'your_database'
  AND  table_type   = 'BASE TABLE'
ORDER BY table_rows DESC;

-- pg_stat_user_tables から概算行数を取得
SELECT schemaname,
       relname     AS table_name,
       n_live_tup  AS row_count
FROM   pg_stat_user_tables
ORDER BY n_live_tup DESC;

-- 統計情報から概算行数を取得（要 ANALYZE 済み）
SELECT table_name, num_rows, last_analyzed
FROM   user_tables
ORDER BY num_rows DESC NULLS LAST;

-- sys.dm_db_partition_stats から正確な行数を取得
SELECT t.name AS table_name,
       SUM(p.rows) AS row_count
FROM   sys.tables t
JOIN   sys.dm_db_partition_stats p
  ON   t.object_id = p.object_id
WHERE  p.index_id IN (0, 1)  -- ヒープ or クラスタ化インデックス
GROUP BY t.name
ORDER BY row_count DESC;

SELECT t.table_name,
       c.column_name,
       c.data_type,
       c.is_nullable,
       c.column_default
FROM   information_schema.tables t
JOIN   information_schema.columns c
  ON   t.table_schema = c.table_schema
  AND  t.table_name   = c.table_name
WHERE  t.table_type = 'BASE TABLE'
  AND  t.table_schema = 'public'  -- スキーマ名は環境に合わせて変更
ORDER BY t.table_name, c.ordinal_position;

SELECT t.table_name,
       c.column_name,
       c.data_type,
       c.nullable,
       c.data_default
FROM   user_tables t
JOIN   user_tab_columns c
  ON   t.table_name = c.table_name
ORDER BY t.table_name, c.column_id;

-- テーブルとビューを種類別に表示
SELECT table_name,
       table_type,
       CASE table_type
         WHEN 'BASE TABLE' THEN '実テーブル'
         WHEN 'VIEW'       THEN 'ビュー'
         ELSE table_type
       END AS type_label
FROM   information_schema.tables
WHERE  table_schema = DATABASE()
ORDER BY table_type, table_name;

-- テーブルとビューを統合して表示
SELECT object_name, object_type
FROM   user_objects
WHERE  object_type IN ('TABLE', 'VIEW')
ORDER BY object_type, object_name;

-- 'email' カラムを持つテーブルを検索
SELECT table_name, column_name, data_type
FROM   information_schema.columns
WHERE  table_schema = DATABASE()
  AND  column_name LIKE '%email%'
ORDER BY table_name;

-- レコード0件のテーブルを表示
SELECT table_name
FROM   information_schema.tables
WHERE  table_schema = DATABASE()
  AND  table_type   = 'BASE TABLE'
  AND  table_rows   = 0;

SELECT table_name,
       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
FROM   information_schema.tables
WHERE  table_schema = DATABASE()
  AND  table_type   = 'BASE TABLE'
ORDER BY total_mb DESC;

-- 直近7日間に変更されたテーブル
SELECT name AS table_name,
       create_date,
       modify_date
FROM   sys.tables
WHERE  modify_date >= DATEADD(day, -7, GETDATE())
ORDER BY modify_date DESC;

-- 本番にあってテストにないテーブル
SELECT table_name, '本番のみ' AS status
FROM   information_schema.tables
WHERE  table_schema = 'production_db'
  AND  table_type   = 'BASE TABLE'
  AND  table_name NOT IN (
    SELECT table_name FROM information_schema.tables
    WHERE  table_schema = 'test_db'
  )
UNION ALL
-- テストにあって本番にないテーブル
SELECT table_name, 'テストのみ' AS status
FROM   information_schema.tables
WHERE  table_schema = 'test_db'
  AND  table_type   = 'BASE TABLE'
  AND  table_name NOT IN (
    SELECT table_name FROM information_schema.tables
    WHERE  table_schema = 'production_db'
  );

-- 外部キーの一覧（どのテーブルがどのテーブルを参照しているか）
SELECT kcu.TABLE_NAME     AS child_table,
       kcu.COLUMN_NAME    AS child_column,
       kcu.REFERENCED_TABLE_NAME  AS parent_table,
       kcu.REFERENCED_COLUMN_NAME AS parent_column
FROM   information_schema.KEY_COLUMN_USAGE kcu
WHERE  kcu.TABLE_SCHEMA = DATABASE()
  AND  kcu.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY child_table, child_column;

-- 外部キーの一覧
SELECT tc.table_name  AS child_table,
       kcu.column_name AS child_column,
       ccu.table_name  AS parent_table,
       ccu.column_name AS parent_column
FROM   information_schema.table_constraints tc
JOIN   information_schema.key_column_usage kcu
  ON   tc.constraint_name = kcu.constraint_name
JOIN   information_schema.constraint_column_usage ccu
  ON   tc.constraint_name = ccu.constraint_name
WHERE  tc.constraint_type = 'FOREIGN KEY'
ORDER BY child_table;

import mysql.connector

conn = mysql.connector.connect(
    host='localhost', user='root',
    password='pass', database='mydb'
)
cursor = conn.cursor()
cursor.execute('SHOW TABLES')

for (table_name,) in cursor:
    print(table_name)

cursor.close()
conn.close()

$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'root', 'pass');
$stmt = $pdo->query('SHOW TABLES');

while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
    echo $row[0] . '\n';
}

// DatabaseMetaData を使う（RDBMS非依存）
Connection conn = DriverManager.getConnection(url, user, pass);
DatabaseMetaData meta = conn.getMetaData();
ResultSet rs = meta.getTables(
    null, null, "%",
    new String[]{"TABLE"}
);
while (rs.next()) {
    System.out.println(rs.getString("TABLE_NAME"));
}