【SQL Server】テーブル一覧を取得するSQL｜sys.tables・INFORMATION_SCHEMA.TABLES・sp_tablesの使い分け

SQL Serverでデータベース内のテーブル一覧を確認したい場合、基本は sys.tables と sys.schemas を使います。テーブル名だけでなく、スキーマ名、作成日、更新日、行数まで一緒に確認できます。

一方で、標準SQL寄りに書きたい場合は INFORMATION_SCHEMA.TABLES、ODBC系の互換的な一覧取得では sp_tables も使われます。この記事では、実務でよく使う順に、SQL Serverのテーブル一覧取得SQLをまとめます。

まず使うSQL

テーブル一覧を確認するだけなら、まず次のSQLで十分です。通常のユーザーテーブルだけを、スキーマ名つきで取得します。

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    t.create_date,
    t.modify_date
FROM sys.tables AS t
JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
ORDER BY
    s.name,
    t.name;

sys.tables はSQL Serverデータベース内のユーザーテーブルを返すカタログビューです。スキーマ名は sys.schemas 側にあるため、実務ではJOINして表示することが多いです。

現在見ているデータベースを確認する

テーブル一覧を確認するときは、最初に接続先のデータベースを確認しておくと安全です。SQL Server Management Studioやアプリの接続文字列で、想定と違うデータベースを見ていることがあります。

SELECT DB_NAME() AS current_database;

-- 必要なら対象DBへ切り替える
USE YourDatabaseName;
GO

sys.tables や INFORMATION_SCHEMA.TABLES は、基本的に現在接続しているデータベース内の情報を返します。一覧に目的のテーブルが出てこない場合は、SQLの前に接続先DBを疑うと切り分けが速いです。

sys.tablesでテーブル一覧を取得する

sys.tables は、現在のデータベースにあるユーザーテーブルを確認するための代表的な方法です。テーブル名、作成日、更新日、メモリ最適化テーブルかどうか、テンポラルテーブルかどうかなど、SQL Server固有の情報も確認できます。

SELECT
    t.object_id,
    SCHEMA_NAME(t.schema_id) AS schema_name,
    t.name AS table_name,
    t.create_date,
    t.modify_date,
    t.is_memory_optimized,
    t.temporal_type_desc
FROM sys.tables AS t
ORDER BY
    schema_name,
    table_name;

SCHEMA_NAME(t.schema_id) を使えば、JOINしなくてもスキーマ名を表示できます。ただし、スキーマに関する条件や追加情報を扱うなら sys.schemas とJOINする書き方のほうが拡張しやすいです。

スキーマを指定して取得する

dbo だけ、または特定スキーマだけのテーブル一覧を見たい場合は、sys.schemas とJOINして s.name で絞ります。

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    t.create_date,
    t.modify_date
FROM sys.tables AS t
JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
WHERE s.name = 'dbo'
ORDER BY t.name;

SQL Serverでは、同じテーブル名でもスキーマが違えば別オブジェクトとして扱われます。そのため、テーブル一覧ではテーブル名だけでなくスキーマ名も一緒に出すのが安全です。

テーブル名で検索する

テーブル名の一部だけ覚えている場合は、LIKE で検索します。

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    t.create_date,
    t.modify_date
FROM sys.tables AS t
JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
WHERE t.name LIKE '%Order%'
ORDER BY
    s.name,
    t.name;

前方一致で探せるなら LIKE 'Order%' のように書くと読みやすいです。大量のオブジェクトを持つデータベースでは、あいまい検索を広げすぎないようにします。LIKE条件やインデックスの効き方は SQL Serverでインデックスが使われない原因 も参考になります。

行数も一緒に確認する

テーブル一覧と一緒に、おおよその行数を確認したい場合は sys.partitions を使えます。sys.partitions.rows はパーティションごとの概算行数として扱います。

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    SUM(p.rows) AS row_count
FROM sys.tables AS t
JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
JOIN sys.partitions AS p
    ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1)
GROUP BY
    s.name,
    t.name
ORDER BY row_count DESC;

p.index_id IN (0, 1) は、ヒープまたはクラスタ化インデックス側の行数を見るための条件です。非クラスタ化インデックスまで合計すると、同じテーブルの行数を重複して数えてしまいます。

作成日・更新日で並べる

最近作成されたテーブルや、最近定義変更されたテーブルを探す場合は、create_date や modify_date で並べ替えます。

-- 最近作成されたテーブル
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    t.create_date,
    t.modify_date
FROM sys.tables AS t
JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
ORDER BY t.create_date DESC;

-- 最近定義変更されたテーブル
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    t.create_date,
    t.modify_date
FROM sys.tables AS t
JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
ORDER BY t.modify_date DESC;

modify_date はテーブル定義の変更日時を見るための情報です。データ行がINSERT/UPDATEされた最終日時ではない点に注意してください。データ更新の時刻を追いたい場合は、アプリケーション側で更新日時列を持たせる設計が必要です。

INFORMATION_SCHEMA.TABLESで取得する

INFORMATION_SCHEMA.TABLES でもテーブル一覧を取得できます。標準SQL寄りで読みやすく、テーブルとビューを TABLE_TYPE で判別できます。

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

TABLE_TYPE = 'BASE TABLE' を指定すると、ビューを除外して通常テーブルだけを取得できます。ビューも含めたい場合は条件を外すか、TABLE_TYPE = 'VIEW' を指定します。

Microsoftのドキュメントでも、INFORMATION_SCHEMA はメタデータの一部だけを表す可能性があり、スキーマなどを確実に調べるにはカタログビューを使う旨が説明されています。SQL Serverで管理用SQLを書くなら、基本は sys.tables 系を選ぶのが無難です。

ビュー一覧も見たい場合

sys.tables はユーザーテーブルの一覧です。ビュー一覧を見たい場合は sys.views を使います。テーブルとビューをまとめて見たい場合は、sys.objects で種別を分けて取得します。

-- ビュー一覧
SELECT
    s.name AS schema_name,
    v.name AS view_name,
    v.create_date,
    v.modify_date
FROM sys.views AS v
JOIN sys.schemas AS s
    ON v.schema_id = s.schema_id
ORDER BY s.name, v.name;

-- テーブルとビューをまとめて取得
SELECT
    s.name AS schema_name,
    o.name AS object_name,
    o.type_desc
FROM sys.objects AS o
JOIN sys.schemas AS s
    ON o.schema_id = s.schema_id
WHERE o.type IN ('U', 'V')
ORDER BY s.name, o.type_desc, o.name;

o.type = 'U' はユーザーテーブル、o.type = 'V' はビューです。通常テーブルだけを見たいのか、ビューも含めたいのかを先に決めると、一覧SQLがぶれにくくなります。

sp_tablesで取得する

sp_tables は、現在の環境で問い合わせ可能なテーブルやビューの一覧を返すシステムストアドプロシージャです。ODBCの SQLTables に相当する位置づけで、簡易確認やツール連携で見かけることがあります。

-- 問い合わせ可能なテーブルやビューを一覧表示
EXEC sp_tables;

-- dboスキーマのテーブルだけ確認する
EXEC sp_tables
    @table_name = '%',
    @table_owner = 'dbo',
    @table_type = '''TABLE''';

sp_tables は手早く確認するには便利ですが、作成日や更新日、テンポラルテーブル情報、行数などを組み合わせたい場合は、sys.tables から組み立てるほうが実務向きです。

見えないテーブルがあるときの確認点

テーブル一覧に目的のテーブルが出てこない場合は、SQLが間違っているとは限りません。現在接続しているデータベース、権限、スキーマ、ビューとの取り違えを確認します。

テーブル一覧ではなく、テーブルロックやブロッキングを確認したい場合は、SQL Serverでテーブルロックを確認するSQL のように sys.dm_tran_locks など別のビューを使います。一覧取得とロック調査は目的が違うので、SQLも分けて考えます。

一時テーブルを確認したい場合

#temp のようなローカル一時テーブルは、ユーザーデータベースではなく tempdb 側に作られます。現在のデータベースで sys.tables を見ても出てこないため、tempdb.sys.tables を確認します。

-- 現在セッションで作った #temp などを探す
SELECT
    name AS temp_table_name,
    create_date
FROM tempdb.sys.tables
WHERE name LIKE '#%'
ORDER BY create_date DESC;

ローカル一時テーブルは内部的に名前が付け替えられるため、表示される名前が #temp そのものではなく、末尾に識別用の文字列が付いた形になることがあります。

テーブル一覧を実務で使う例

実務では、単に一覧を見るだけでなく、棚卸しや調査に使うことが多いです。次のように目的別のSQLを用意しておくと、調査が速くなります。

-- 1. dbo以外のスキーマにあるテーブル
SELECT s.name AS schema_name, t.name AS table_name
FROM sys.tables AS t
JOIN sys.schemas AS s ON t.schema_id = s.schema_id
WHERE s.name <> 'dbo'
ORDER BY s.name, t.name;

-- 2. 名前に log を含むテーブル
SELECT s.name AS schema_name, t.name AS table_name
FROM sys.tables AS t
JOIN sys.schemas AS s ON t.schema_id = s.schema_id
WHERE t.name LIKE '%log%'
ORDER BY s.name, t.name;

-- 3. 行数が多いテーブル上位
SELECT TOP (20)
    s.name AS schema_name,
    t.name AS table_name,
    SUM(p.rows) AS row_count
FROM sys.tables AS t
JOIN sys.schemas AS s ON t.schema_id = s.schema_id
JOIN sys.partitions AS p ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1)
GROUP BY s.name, t.name
ORDER BY row_count DESC;

行数が多いテーブルや更新頻度が高いテーブルを調べる場合は、統計情報や実行計画の確認にもつながります。SQL Serverの統計情報を更新する方法 もあわせて見ると、遅いSQL調査の流れがつかみやすくなります。

よくある質問

SQL Serverでテーブル一覧を見る一番おすすめのSQLはどれですか？

sys.tables と sys.schemas をJOINするSQLがおすすめです。スキーマ名、作成日、更新日、SQL Server固有の属性を確認しやすいためです。

INFORMATION_SCHEMA.TABLESだけで十分ですか？

簡単な一覧取得なら十分なこともあります。ただし、SQL Server固有の詳細情報や新しい機能に関するメタデータまで扱うなら、sys.tables などのカタログビューを使うほうが安全です。

テーブル一覧にビューが混ざるのはなぜですか？

INFORMATION_SCHEMA.TABLES や sp_tables はビューも返すことがあります。通常テーブルだけにしたい場合は、TABLE_TYPE = 'BASE TABLE' や @table_type = '''TABLE''' のように条件を指定します。

テーブルのデータ更新日時は確認できますか？

sys.tables.modify_date はテーブル定義の変更日時であり、行データの最終更新日時ではありません。データの更新日時を正確に追うには、更新日時列をテーブルに持たせる設計が必要です。日時列の扱いは SQL Serverでdatetimeから日付のみを取得する方法 も参考になります。

まとめ

SQL Serverでテーブル一覧を取得するなら、基本は sys.tables と sys.schemas のJOINです。スキーマ名、テーブル名、作成日、更新日を自然に確認でき、sys.partitions と組み合わせれば概算行数も取得できます。

INFORMATION_SCHEMA.TABLES は簡単な一覧取得や標準SQL寄りの書き方に向いていますが、SQL Serverの管理用SQLでは sys.tables のほうが情報量が多く扱いやすいです。sp_tables は互換性や簡易確認用として使い分けましょう。

参考

sys.tables – Microsoft Learn

sys.schemas – Microsoft Learn

TABLES / INFORMATION_SCHEMA.TABLES – Microsoft Learn

sp_tables – Microsoft Learn

sys.partitions – Microsoft Learn