【SQL Server】SSMSのテーブル毎のデータ使用量レポート相当の出力を得るSQL作った【運用保守】 │ コジマノテック

1. はじめに
2. さいごに
3. もっと学びたい人はこちら

はじめに

コジマです。

SSMS使うとテーブル毎のデータ使用量をレポートで出す機能あるんですけど、
それを使ってExcelに出すとData列がなぜがセル結合されているし、合計が出せないので
すごく悶々としてました。

というわけで作りました。

/* SSMS相当のテーブル利用状況の取得 */
-- テーブル名の一覧取得
DECLARE tables CURSOR
    FOR 
        SELECT CONCAT(s.name, '.', t.name)
        FROM sys.tables t
            JOIN sys.schemas s ON t.schema_id = s.schema_id
            JOIN sys.objects o ON t.object_id = o.object_id
        WHERE t.[type] = 'U' -- ユーザーテーブルのみ取得
            AND o.[type] = 'U'  -- ユーザーオブジェクトのみ取得
        ORDER BY s.schema_id, t.name
        ;
DECLARE @table_name NVARCHAR(100);

-- 一時テーブルの作成
DROP TABLE IF EXISTS #SPACEUSED;
CREATE TABLE #SPACERUSED (
    [name] NVARCHAR(100) --COLLATE Japanese_XJIS_140_CS_AS_KS_WS -- エラーでたら照合順序を変更する必要がある
    , [rows] int
    , [reserved] NVARCHAR(20)
    , [data] NVARCHAR(20)
    , [index_size] NVARCHAR(20)
    , [unused] NVARCHAR(20)
);
-- 一個目のテーブル取得
OPEN tables;
FETCH NEXT FROM tables INTO @table_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- テーブルにINSERTする
    INSERT INTO #SPACERUSED
    exec sp_spaceused @table_name;
    PRINT 'INSERT FROM ' + @table_name

    -- 異常終了したら終わり
    IF @@FETCH_STATUS <> 0
        PRINT 'DONE'
    -- 正常終了したら次
    IF @@FETCH_STATUS = 0
        FETCH NEXT FROM tables INTO @table_name;
END
CLOSE tables;
DEALLOCATE tables;

-- テーブル取得
SELECT
    s.name AS schemaName -- スキーマ名
    , t.name AS tableName -- テーブル名
    , su.rows AS [rowCounts] -- レコード数
    , CAST(REPLACE(su.reserved, N' KB', '') as int) AS [reserved(KB)] -- 確保済領域(KB)
    , CAST(REPLACE(su.data, N' KB', '') as int) AS [data(KB)] -- 使用領域（KB）
    , CAST(REPLACE(su.index_size, N' KB', '') as int) AS [index_size(KB)] -- インデックスサイズ（KB）
    , CAST(REPLACE(su.unused, N' KB', '') as int) AS [unused(KB)] -- 未使用領域（KB）
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN #SPACERUSED su ON t.[name] = su.[name]
WHERE t.type = 'U'
UNION 
SELECT
    N'ZZZ' AS schemaName -- スキーマ名（ORDER BYで最後に来るようにしている）
    , 'TOTAL' AS tableName -- テーブル名
    , sum(CAST(REPLACE(su.rows, N' KB', '') as int)) AS [rowCounts] -- レコード数
    , sum(CAST(REPLACE(su.reserved, N' KB', '') as int)) AS [reserved(KB)] -- 確保済領域(KB)
    , sum(CAST(REPLACE(su.data, N' KB', '') as int)) AS [data(KB)] -- 使用領域（KB）
    , sum(CAST(REPLACE(su.index_size, N' KB', '') as int)) AS [index_size(KB)] -- インデックスサイズ（KB）
    , sum(CAST(REPLACE(su.unused, N' KB', '') as int)) AS [unused(KB)] -- 未使用領域（KB）
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN #SPACERUSED su ON t.[name] = su.[name]
WHERE t.type = 'U'
ORDER BY schemaName, tableName
;

こんな感じで出力されます。

最後には合計も出ます。

あとはExcelにでも吐いて煮るなり焼くなりできます。

さいごに

結構汎用性あると思います。
DBによって照合順序エラー出るときあるので、そのときは

[name] NVARCHAR(100) --COLLATE Japanese_XJIS_140_CS_AS_KS_WS -- エラーでたら照合順序を変更する必要がある

の行のコメント外してあげてください。

この記事を面白いまたは役に立ったと思ってくれた方は是非私のTwitter(@kojimanotech)を
フォローしてくれたらうれしいです！

システムエンジニアのつらい部分のあるあるなんかをエンタメにしたチャンネルを作りました。
チャンネルはこちら
つらい部分も楽しくなればと思っているのでよかったらチャンネル登録や高評価してくれたらうれしいです。

もっと学びたい人はこちら

SQLをもっと学びたいエンジニアのためにおすすめのUdemy講座を紹介いたします！

SQLはじめましての人！まずあなたの３時間をください。
この講座でまずこれだけは覚えてくれ！という内容をしっかり押さえることができます。
SQLはプログラミング言語がなんであれ、システム開発に必要になります。
この講座を見て最低限必要な知識を身に着けて実践で貢献できるようになりましょう！
3時間で学ぶ SQL ・データベース超入門【丁寧な解説+演習問題で SQL データ抽出の基本が身につく】標準 SQL

データベースの設計について学べる講座です。
設計について学べる教材は少ないので、実務でデータベースを触る人にはとてもうれしい講座です。
データベース講座１：データベース論理設計

ちょっとニッチに感じるかもしれませんが、SQLのインデックスを学ぶことができる講座です。
よい性能でSQLを運用するためにはインデックスの理解は必要不可欠です。
CRUD書いて満足するエンジニアを出し抜きましょう。
SQLServerのインデックスを理解して検索を速くする方法

気になった人はぜひ見てみてくださいね！

以上、コジマでした。