If lost it you have, find it you should!

This is a very short blog as I’ve been a tad busy of late!….

I’ve been working with partitioning recently in a big Data Warehouse, and since this has involved file-groups, data-files etc.. and moving *lot* of data around, I wanted to make sure I knew where I’d put everything (both for partitioned and non-partitioned data)…

Enter my – Where is everything script:

SELECT
SCHEMA_NAME(ss.schema_id) AS [Schema Name],
OBJECT_NAME(db_ps.object_id) AS [Table Name],
db_ps.index_id,
CASE ISNULL(si.name, ‘0’)
WHEN ‘0’ THEN ‘No Indexes’
ELSE si.name
END AS [Index Name],
si.type_desc,
COALESCE(sfg1.name, sfg2.name) AS FileGroupName,
ISNULL(ps.name, ‘Not Partitioned’) AS [PartitionSchemeName],
db_ps.partition_number AS PartitionNumber,
db_ps.row_count
FROM
sys.partitions p
INNER JOIN sys.dm_db_partition_stats db_ps ON p.partition_id = db_ps.partition_id
AND p.object_id = db_ps.object_id
INNER JOIN sys.indexes si ON p.OBJECT_ID = si.OBJECT_ID AND p.index_id = si.index_id
INNER JOIN sys.objects so ON so.object_id = si.object_id
INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id
LEFT JOIN sys.partition_schemes ps ON ps.data_space_id = si.data_space_id
LEFT JOIN sys.partition_functions pf ON pf.function_id = ps.function_id
LEFT JOIN sys.partition_range_values pv ON pf.function_id = pv.function_id
AND p.partition_number = pv.boundary_id
LEFT JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
LEFT JOIN sys.partition_parameters pp ON pf.function_id = pp.function_id
LEFT JOIN sys.types t ON t.system_type_id = pp.system_type_id
LEFT JOIN sys.data_spaces ds ON ds.data_space_id=dds.data_space_id
LEFT JOIN sys.filegroups sfg1 ON sfg1.data_space_id = dds.data_space_id
LEFT JOIN sys.filegroups sfg2 ON si.data_space_id = sfg2.data_space_id
WHERE
so.type = ‘U’
AND si.index_id IN (0,1) — Heaps or Clustered Indexes
ORDER BY
[Schema Name] ASC, [Table Name] ASC, PartitionNumber ASC

Which does just the job for me….