August 2, 2012 at 4:51 am
Does anyone have a query to get the Logical Filename, Index Name, partition number for a specific table?
Thanks
August 2, 2012 at 7:28 am
SELECT ps.name AS PSName,
dds.destination_id AS PartitionNumber,
fg.name AS FileGroupName,fg.name,
t.name
FROM (((sys.tables AS t
INNER JOIN sys.indexes AS i
ON (t.object_id = i.object_id))
INNER JOIN sys.partition_schemes AS ps
ON (i.data_space_id = ps.data_space_id))
INNER JOIN sys.destination_data_spaces AS dds
ON (ps.data_space_id = dds.partition_scheme_id))
INNER JOIN sys.filegroups AS fg
ON dds.data_space_id = fg.data_space_id
where t.name ='tableName'
August 2, 2012 at 7:44 am
Thanks for the query, but it isn't returning the correct data.
The logical filename is present in select sys.master_files - I just can't workout how to tie this to the other tables (for each partition number)
August 2, 2012 at 7:55 am
SELECT ps.name AS PSName,
dds.destination_id AS PartitionNumber,
fg.name AS FileGroupName,fg.name,
t.name,
f.name as filename
FROM (((sys.tables AS t
INNER JOIN sys.indexes AS i
ON (t.object_id = i.object_id))
INNER JOIN sys.partition_schemes AS ps
ON (i.data_space_id = ps.data_space_id))
INNER JOIN sys.destination_data_spaces AS dds
ON (ps.data_space_id = dds.partition_scheme_id))
INNER JOIN sys.filegroups AS fg
ON dds.data_space_id = fg.data_space_id
inner join sys.database_files f on f.data_space_id = fg.data_space_id
August 2, 2012 at 8:23 am
Great. Thanks for your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply