Handy query to find allocation units for a table.

Find below a handy query to find allocation units

-- Create table Test
CREATE TABLE Test (col1 int identity, col2 varchar(7000), col3 varchar(2000), col4 nvarchar(max))

-- Insert a single row in this table to ensure the page size exceeds 8060 bytesINSERT INTO Test VALUES (REPLICATE('a',7000),REPLICATE('b',2000),REPLICATE('c',8070));

-- Fire the query and see the result
SELECT o.name
,i.index_id
,i.name
,a.type_desc
,a.data_pages
,p.partition_number
,a.type
,a.total_pages
FROM sys.allocation_units a
INNER JOIN
sys.partitions p
ON a.container_id = p.partition_id
INNER JOIN
sys.objects o
ON o.object_id = p.object_id
INNER JOIN
sys.indexes i
ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE o.name = 'Test'
ORDER BY o.name, i.index_id