SQL Server - 全テーブルのレコード数を一覧で取得する方法

SQL Server で全テーブルのレコード数を一覧で取得する方法

SQL Server で分析をしている時に、データベース内の全てのテーブル名と、それぞれのレコード数の一覧を取得したいと思ったことはありませんか？

この記事では、SQL Server で全テーブルのレコード数の一覧を効率的に取得する方法をご紹介します。

目次
  1. sys.dm_db_partition_stats を使う方法
  2. 各テーブルに対して COUNT(*) を実行する方法

sys.dm_db_partition_stats を使う方法

SQL Server 上で全テーブルのレコード数を一覧で取得するひとつ目の方法は、sys.dm_db_partition_stats を使う方法です。

sys.dm_db_partition_stats は SQL Server のシステムビューで、現在のデータベースのパーティションごとに、ページ数と行数などの情報を返します。


sys.dm_db_partition_stats を使って全テーブルのレコード数を一覧で取得するには、次のようにできます。

SELECT  S.name AS SchemaName,
        T.name AS TableName,
        SUM(P.row_count) AS RecordCount
FROM    sys.tables AS T
            INNER JOIN sys.schemas AS S
                ON T.schema_id = S.schema_id
            INNER JOIN sys.dm_db_partition_stats AS P
                ON T.object_id = P.object_id
WHERE   P.index_id IN (0, 1) -- 0: Heap, 1: Clustered index
GROUP BY S.name,
         T.name
ORDER BY RecordCount DESC;

[実行結果]
SQL Server - 全テーブルのレコード数を一覧で取得する方法 1


システムビューの sys.tables と sys.schemas と sys.dm_db_partition_stats を INNER JOIN して、スキーマ名、テーブル名でグルーピングして、レコード数を取得するのに、sys.dm_db_partition_stats の row_count の合計を計算しています。

sys.dm_db_partition_stats の index_id は、パーティションが属するヒープまたはインデックスの IDで、0: ヒープ と 1: クラスター化インデックスのレコードのみを取得しています。

index_id = 0 のヒープのレコードも取得しないと、クラスター化インデックスのついていないようなテーブルのレコード数が 0 になってしまいます。

また、index_id が 1 より大きいレコードを含めてしまうと、非クラスター化インデックスのレコード数までカウントされてしまい、レコード数が正しい値になりませんのでご注意ください。


sys.dm_db_partition_stats の行数（row_count）は統計情報（sys.stats）とは異なり、実際のパーティションごとの物理情報に基づいています。

この方法は非常に高速に取得できるため、本番環境でも安全に使えますが、完全にリアルタイムの数値とは限りません。

sys.dm_db_partition_stats の row_count は、更新（INSERT/DELETE/TRUNCATE など）に応じて随時更新されますが、未コミットのトランザクションは反映されません。

また、長時間のトランザクションや一部のキャッシュの影響で、最新の状態とわずかにズレることがあります。

それでも、「ほぼ正確」な件数を負荷なく得られるため、通常の分析には十分かと思われます。


各テーブルに対して COUNT(*) を実行する方法

SQL Server 上で全テーブルのレコード数を一覧で取得するふたつ目の方法は、SELECT COUNT(*) を使う方法です。

システムビューの sys.tables と sys.schemas から、ダイナミックに SQL 文を生成し、スキーマ名、テーブル名、 COUNT(*) でレコード数を取得します。

DECLARE @Sql NVARCHAR(MAX) = N'';

SELECT @Sql += 'SELECT ''' + s.name + ''' AS SchemaName, '''
                + t.name + ''' AS TableName, COUNT(*) AS RecordCount FROM ['
                + s.name + '].[' + t.name + '] UNION ALL ' + CHAR(13) + CHAR(10)
FROM    sys.tables AS T
            INNER JOIN sys.schemas AS S
                ON T.schema_id = S.schema_id;

SET @Sql = LEFT(@Sql, LEN(@Sql) - LEN(' UNION ALL ' + CHAR(13) + CHAR(10)));
SET @Sql += ' ORDER BY RecordCount DESC;'

EXEC sp_executesql @Sql;

[実行結果]
SQL Server - 全テーブルのレコード数を一覧で取得する方法 2

生成され、実行された @Sql は以下の通りです。

SELECT 'dbo' AS SchemaName, 'BudgetTable' AS TableName, COUNT(*) AS RecordCount FROM [dbo].[BudgetTable] UNION ALL
SELECT 'dbo' AS SchemaName, 'Student' AS TableName, COUNT(*) AS RecordCount FROM [dbo].[Student] UNION ALL
SELECT 'dbo' AS SchemaName, 'Test' AS TableName, COUNT(*) AS RecordCount FROM [dbo].[Test] UNION ALL
SELECT 'dbo' AS SchemaName, 'TestResult' AS TableName, COUNT(*) AS RecordCount FROM [dbo].[TestResult] UNION ALL
SELECT 'dbo' AS SchemaName, 'TestResultSummary' AS TableName, COUNT(*) AS RecordCount FROM [dbo].[TestResultSummary] UNION ALL
SELECT 'dbo' AS SchemaName, 'Customer' AS TableName, COUNT(*) AS RecordCount FROM [dbo].[Customer] UNION ALL
SELECT 'dbo' AS SchemaName, 'Sales' AS TableName, COUNT(*) AS RecordCount FROM [dbo].[Sales] ORDER BY RecordCount DESC;

ひとつの結果セットにまとめたいので UNION ALL を追加し、本来テーブル一覧とレコード数を取得するだけであれば必要ありませんが、今回生成されたクエリーの読みやすさのために CHAR(13) と CHAR(10) で改行を挿入しています。

SET @Sql = LEFT(@Sql, LEN(@Sql) - LEN(' UNION ALL ' + CHAR(13) + CHAR(10))) では、最後の行の UNION ALL と改行を削除しています。

その後で、レコード数が多い順に並べたいので、' ORDER BY RecordCount DESC;' を追加しています。

先ほどと同じ全テーブルとレコード数の一覧が取得できていますね。


この方法は先ほどの sys.dm_db_partition_stats を使う方法と違い、正確なレコード数が得られます。

ですが、各テーブルやインデックスなどをフルスキャンするため、パフォーマンスへの影響が大きいので、本番環境で実行する際には注意が必要です。


以上、SQL Server で全テーブルのレコード数の一覧を効率的に取得する方法をご紹介しました。

© 2010-2025 SQL Server 入門