I am upgrading a database from using old style partitioning to new style (2008) partitioning. It has 200,000,000 + rows and is currently partitioned on Date using horizontal 2000 style partitioning i.e. many tables and a union view.
I wanted to check on the progress of the data import and also make sure the data is going in to the correct file group. Here’s some SQL on how to do this.
object_name(SI.object_id) as objectName
from sys.destination_data_spaces DS
join sys.filegroups F on F.data_space_id = DS.data_space_id
join sys.indexes SI on SI.data_space_id = DS.partition_scheme_id
join sys.partitions SP on SP.object_id = SI.object_id
and SI.index_id = SP.index_id
and SP.partition_number = DS.destination_id