Find Partition Schema Definitions in SQL Server Database

Question

I have access to a database and I need to know the Partition Scheme definitions in the database. i.e. I need to know the partition scheme name, which Partition function is it using, what file groups are the partitions assigned, etc...

For example someone creates a partition scheme as so (taken from msdn):

CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg);

Then I want the name: myRangePS1, the function: myRangePF1, and the partitions: (test1fg, test2fg, test3fg, test4fg), Whether it is partition ALL or not

How would I go about this using SQL statements only? I can query the names and some data about partitions by using the system view sys.partition_scheme, but it is not enough.

The below shows a similar solution on finding the definition of Partition functions: http://social.msdn.microsoft.com/forums/sqlserver/en-US/d0ce92e3-bf48-455d-bd89-c334654d7e97/how-to-find-partition-function-text-applied-to-a-table

Answer 1

I have modified knkarthick24's first query to show Partition function values associated to each file group:

select distinct ps.Name AS PartitionScheme, pf.name AS PartitionFunction,fg.name AS FileGroupName, rv.value AS PartitionFunctionValue
    from sys.indexes i  
    join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id  
    join sys.partition_schemes ps on ps.data_space_id = i.data_space_id  
    join sys.partition_functions pf on pf.function_id = ps.function_id  
    left join sys.partition_range_values rv on rv.function_id = pf.function_id AND rv.boundary_id = p.partition_number
    join sys.allocation_units au  ON au.container_id = p.hobt_id   
    join sys.filegroups fg  ON fg.data_space_id = au.data_space_id  
where i.object_id = object_id('TableName') 

This is the query I was looking for and I hope other people can make use of this!