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

はじめに

コジマです。

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)を
フォローしてくれたらうれしいです！

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

以上、コジマでした。