By Richie Lee

I recently wanted to find out how big each of my partitions are in a data warehouse. It’s seriously useful for estimating size requirements. The script below is the result. It might require a bit of tweaking to suit the layout of your partitions (see comments in the script) but for the most part it’s good to go. It took a few seconds to run on a database with many partition schemas/functions and many thousands of partitions, however I advise in testing it out in a test environment as it will cause locking on system tables.



declare @schema varchar (16)
declare @psc varchar (128)
declare @pfn varchar (128)
--assuming all partitioned tables are in the same schema, set the schema name
--if this is not the case then the inner select to get the names of the tables will not work
--at this point you will want to update that inner select to get the names of the tables, either by 
--updating the inner query or just literally naming the tables you want 
set @schema = <schemaname>

--set function and schema names
--adding these into the query below
--speeds up the query A LOT
set @pfn = <pfnname>
set @psc = <pscName>

-- you may even want to build up a variable for each of these based on the 
--schema, or some other arbitrary setting 
--declare @prefix varchar (4)
--set @prefix = case when @schema = 'dbo' then '' else 'Sales' end
--set @pfn = 'pfn'+@prefix
--set @psc = 'psc'+@prefix

SELECT 
p.partition_number AS PartitionNumber,
prv.value as RangeValue,
pf.name as pfnName,
SUM(a.total_pages) /128 AS TotalSpaceMB, 
SUM(a.used_pages) /128 AS UsedSpaceMB, 
(SUM(a.total_pages) - SUM(a.used_pages)) /128 AS UnusedSpaceMB
FROM sys.tables t

INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id 
INNER JOIN sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id 
INNER JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id 
INNER JOIN sys.partition_range_values prv with (nolock) on prv.function_id = pf.function_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.name IN (--getting all of the partitioned tables on the database schema
 select t.name from sys.tables t 
 inner join sys.schemas s on s.schema_id = t.schema_id
 where s.name = @schema
 and t.name LIKE '%')
 and ps.name = @psc
 and pf.name = @pfn
 and prv.boundary_id = p.partition_number 
GROUP BY p.partition_number, prv.value, pf.name
ORDER BY TotalSpaceMB desc