Useful partition script


Retrieve filegroup by table name and partition range


The following script can be used to retrieve the filegroup name for a given table and a partition range.  It can be very useful in an automated sliding window process.


declare @tablename nvarchar(128)


declare @range sql_variant


 


set @tablename = ‚myPartitionedTable‘


set @range = 1209


 


declare @tableId int


declare @FGName nvarchar(128)


 


set @tableId = object_id(@tablename)


select


 


@FGName = main.name


from


       (


             select


                    a.partition_number,


                    c.name,


                    e.function_id


             from


                    sys.partitions a


                    inner join sys.allocation_units b


                           on a.hobt_id = b.container_id


                    inner join sys.data_spaces c


                           on b.data_space_id = c.data_space_id


                    inner join sys.destination_data_spaces d


                           on c.data_space_id = d.data_space_id


                    inner join sys.partition_schemes e


                           on d.partition_scheme_id = e.data_space_id


             where a.object_id = @tableId


       ) main


       inner join


             (


             select


                    a.function_id,


                    b.value,


                    case when a.boundary_value_on_right = 0 then b.boundary_id else b.boundary_id + 1 end partition_id


             from


                    sys.partition_functions a


                    inner join sys.partition_range_values b


                           on a.function_id = b.function_id


             where


                    b.value = @range


       ) part


on main.function_id = part.function_id and main.partition_number = part.partition_id


 

Comments (2)

  1. Anonymous says:

    Thanks for your help, that saved me a lot of time.

  2. Anonymous says:

    hi ,

    please help me in getting all

    file names with corresponding  partition range value

    thanks

Skip to main content