[SQLServer]インスタンス内の全テーブル一覧取得(テーブルサイズ付き)

DECLARE @dbname nvarchar(128)
DECLARE @sql nvarchar(4000)

if object_id('tempdb..#db_table_names') is not null
begin
	drop table tempdb..#db_table_names
end

create table #db_table_names(
	dbname nvarchar(128),
	tablename nvarchar(128),
	tablesize float(40)
)

DECLARE MY_CURSOR CURSOR 
FOR 
SELECT name FROM sys.databases where state_desc != 'OFFLINE'

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
	if exists (select * from sys.database_mirroring where database_id=DB_ID(@dbname) and (mirroring_guid is null or mirroring_role_desc='PRINCIPAL'))
	begin
		select @sql =
			N'insert into #db_table_names ' +
				N'select ' +
					N'''' + @dbname + '''' + ' as dbname, ' +
					N'tb.name, ' +
					N'SUM(pt.reserved_page_count) * 8.0 / 1024 as size_mb ' +
				N'from ' + @dbname + '.sys.dm_db_partition_stats as pt  ' +
				N'inner join ' + @dbname + '.sys.tables as tb ' +
					N'on pt.object_id = tb.object_id ' +
				N'where ' +
					N'is_ms_shipped=0 ' +
				N'group by ' +
					N'tb.object_id, ' +
					N'tb.name '
		PRINT @sql
		EXEC sys.sp_executesql @sql
	end
    FETCH NEXT FROM MY_CURSOR INTO @dbname
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

select * from #db_table_names
	where
		dbname not in ('master', 'model', 'msdb', 'tempdb', 'distribution')
	order by dbname,tablename