メモ：全DB、全ユーザーテーブルに対しての雑な sp_spaceused もどき

SQL Server で、クエリ1発だけで全てのデータベースの全てのユーザーテーブル に sp_spaceused の結果が欲しい + 取得日時とかDB名も欲しいなーなんて時のメモ。
雑なので、色々端折ってます。
ちゃんとしたの欲しい人は、クエリ1発諦めてカーソル + 動的SQL(sp_spaceused 呼んだ結果を一時テーブル入れてとかなんやら)とかでやるか、sp_spaceused のソース見てちゃんとやるように改造するかしたらいーんじゃないかな？

exec sp_MSforeachdb 'use [?];
select getdate() as [collect_date]
  , db_name() as [database_name]
  , object_name([object_id]) as [table_name]
  , sum([rows]) as [rows]
  , sum([reserved]) as [reserved_kb]
  , sum([pages]) as [data_kb]
  , sum([used_page]) - sum([pages]) as [index_size_kb]
  , sum([reserved]) - sum([used_page]) as [unused_kb]
from (
  select [object_id]
    , sum([reserved_page_count]) * 8 as [reserved]
    , sum([used_page_count]) * 8 as [used_page]
    , sum(case
          when ([index_id] < 2) then ([in_row_data_page_count] + [lob_used_page_count] + [row_overflow_used_page_count])
          else 0
        end
    ) * 8 as [pages]
    , sum(case
          when ([index_id] < 2) then [row_count]
          else 0
        end
    ) as [rows]
  from [sys].[dm_db_partition_stats]
  where [object_id] in (select [objects].[object_id] from [sys].[objects] where [type] = ''U'')
  group by [object_id]
  union
  select [it].[parent_id]
    , sum([p].[reserved_page_count])
    , sum([p].[used_page_count])
    , 0
    , 0
  from [sys].[dm_db_partition_stats] [p] inner join [sys].[internal_tables] [it]
    on [p].[object_id] = [it].[object_id]
  where [it].[parent_id] in (select [objects].[object_id] from [sys].[objects] where [type] = ''U'') 
    and [it].[internal_type] IN (202,204,207,211,212,213,214,215,216,221,222,236) 
  group by [it].[parent_id]
) [_target]
group by [object_id]
'

tempdb はじくとかてきとーにやってください。