[SqlServer,SqlOs] Allocation Units per object. OUTER APPLY / TOP(1) pattern.

Allocation Units

DECLARE @ObjectId INT
SET @ObjectId = OBJECT_ID('SchemaName.TableName')

SELECT AU.*,
       COALESCE(P1.object_id, P2.object_id) OBJECT_ID,
       COALESCE(P1.index_id, P2.index_id) INDEX_ID
  FROM sys.allocation_units AU
       OUTER APPLY (SELECT TOP(1) P.object_id, 
                           P.index_id
                      FROM sys.partitions P
                     WHERE P.hobt_id = AU.container_id AND AU.type IN (1, 3)) P1
	   OUTER APPLY (SELECT TOP(1) P.object_id, 
	                       P.index_id
                      FROM sys.partitions P
                     WHERE P.partition_id = AU.container_id AND AU.type = 2) P2
 WHERE COALESCE(p1.object_id, p2.object_id) = @ObjectId