[SQLServer]インスタンス内の全データベースサイズ取得

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

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

create table #db_size_mb(
	dbname nvarchar(128),
	size_mb int
)

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_size_mb ' +
			N'select ' + 
				'''' + @dbname+ '''' + ', ' + 
				N'size*8/1024 as size_MB ' +
			N'from '+ @dbname + '.sys.database_files '+
			N'where type_desc = ''ROWS'' '
		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_size_mb
	where
		dbname not in ('master', 'model', 'msdb', 'tempdb','distribution')
	order by dbname,size_mb