はじめに
コジマです。
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はじめましての人!まずあなたの3時間をください。
この講座でまずこれだけは覚えてくれ!という内容をしっかり押さえることができます。
SQLはプログラミング言語がなんであれ、システム開発に必要になります。
この講座を見て最低限必要な知識を身に着けて実践で貢献できるようになりましょう!
3時間で学ぶ SQL ・データベース 超入門【丁寧な解説+演習問題で SQL データ抽出の基本が身につく】標準 SQL
データベースの設計について学べる講座です。
設計について学べる教材は少ないので、実務でデータベースを触る人にはとてもうれしい講座です。
データベース講座1:データベース論理設計
ちょっとニッチに感じるかもしれませんが、SQLのインデックスを学ぶことができる講座です。
よい性能でSQLを運用するためにはインデックスの理解は必要不可欠です。
CRUD書いて満足するエンジニアを出し抜きましょう。
SQLServerのインデックスを理解して検索を速くする方法
気になった人はぜひ見てみてくださいね!
以上、コジマでした。