
--------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------- AUSEVICH.RU ---------------------------------------------------------------
-- http://ausevich.ru/subd/poluchenie-svodnoj-informacii-o-razmere-bazy-dannyh-na-diske-s-pomoshhyu-dinamicheskih-administrativnyh-pred   --
--------------------------------------------------------------------------------------------------------------------------------------------

	SELECT 
		-- in MB --
		convert (dec (15,2), convert (dec (15,0), sum(totalPages)) * 8 / 1024) as totalPages_MB,
		convert (dec (15,2), convert (dec (15,0), sum(reservedPages)) * 8 / 1024) as reservedPages_MB,
		convert (dec (15,2), convert (dec (15,0), sum(usedPages)) * 8 / 1024) as usedPages_MB,
		convert (dec (15,2), convert (dec (15,0), sum(dataPages)) * 8 / 1024) as dataPages_MB,
		convert (dec (15,2), convert (dec (15,0), sum(usedPages - dataPages)) * 8 / 1024) as indexPages_MB,
		convert (dec (15,2), convert (dec (15,0), sum(reservedPages - usedPages)) * 8 / 1024) as unusedPages_MB,
		convert (dec (15,2), convert (dec (15,0), sum(totalPages - reservedPages)) * 8 / 1024) as unallocatedPages_MB,
		-- in Pages --
		sum(totalPages) as totalPages,
		sum(reservedPages) as reservedPages,
		sum(usedPages) as usedPages,
		sum(dataPages) as dataPages,
		sum(usedPages - dataPages) as indexPages,
		sum(reservedPages - usedPages) as unusedPages,
		sum(totalPages - reservedPages) as unallocatedPages,
		-- in Percents
		convert (dec (15,2), convert (dec (15,0), sum(dataPages)) / convert (dec (15,0), sum(totalPages)) * 100) as data_pages_Percent,
		convert (dec (15,2), convert (dec (15,0), sum(usedPages - dataPages)) / convert (dec (15,0), sum(totalPages)) * 100) as indexPages_Percent,
		convert (dec (15,2), convert (dec (15,0), sum(reservedPages - usedPages)) / convert (dec (15,0), sum(totalPages)) * 100) as unusedPages_Percent,
		convert (dec (15,2), convert (dec (15,0), sum(totalPages - reservedPages)) / convert (dec (15,0), sum(totalPages)) * 100) as unallocatedPages_Percent
		
	FROM (
			SELECT 
				sum(a.total_pages) as reservedPages,		
				sum(a.used_pages) as usedPages,		
				sum(CASE
						-- XML-Index and FT-Index and semantic index internal tables are not considered "data", but is part of "index_size"
						WHEN it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) THEN 0
						WHEN a.type <> 1 and p.index_id < 2 THEN a.used_pages
						WHEN p.index_id < 2 THEN a.data_pages
						ELSE 0
					END) as dataPages,
				0 as totalPages
			FROM 
				sys.partitions p 
					JOIN sys.allocation_units a 
						ON p.partition_id = a.container_id
					LEFT JOIN sys.internal_tables it 
						ON p.object_id = it.object_id
								
			UNION ALL

			SELECT
				0,
				0,
				0, 
				df.size
			FROM 
				sys.database_files as df
			WHERE 
				df.type_desc = N'ROWS'
		) as p