select distinct PartitionedTable = OBJECT_NAME(SI.object_id),
sysindexes_data_spaceId = SI.data_space_id ,
destination_data_spaces_data_spaceId = DDS.data_space_id,
PhysicalFileName = DF.physical_name , LogicalFileName = DF.name
from sys.indexes AS SI (nolock)
-- Partitioned Table contains Partition schema data_space_id
-- Non-Partitioned Table contains File Group data_space_id
join sys.data_spaces AS DS (nolock)
-- Data_space_id info for Partition schema and File Group
-- Determines PS or FG
on DS.data_space_id = SI.data_space_id
join sys.destination_data_spaces AS DDS
-- Partition schema (Data_space_id) inturn linked to File Group (Data_space_id)
on DS.data_space_id = DDS.partition_scheme_id
join sys.database_files AS DF -- File Group data_space_id with Physical file name
on DF.data_space_id = DDS.data_space_id AND DF.type = 0
where DS.type = 'PS' and
OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U'
and SI.index_id IN(0,1)
order by DDS.data_space_id
go
select distinct PartitionedTable = OBJECT_NAME(SI.object_id) ,DS.data_space_id ,DF.physical_name
from sys.indexes AS SI (nolock)
join sys.data_spaces AS DS (nolock) on DS.data_space_id = SI.data_space_id
join sys.database_files AS DF on DF.data_space_id = DS.data_space_id AND DF.type = 0
where DS.type = 'FG' and
OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U'
and SI.index_id IN(0,1)
--and DS.data_space_id IN ( 65606,65607)
order by DS.data_space_id
go
