SQL Partitions

SQL Query to get partitions information for a table

select t.name as table_name,
    i.name as index_name,
    ds.type_desc,
    ps.name as partition_scheme_name ,pf.name
from sys.tables t
join sys.indexes i on t.object_id = i.object_id and t.object_id=1
join sys.data_spaces ds on i.data_space_id = ds.data_space_id
left join sys.partition_schemes ps on ps.data_space_id = ds.data_space_id
inner join
  sys.partition_functions pf on ps.function_id = pf.function_id
 

Where we can see partition functions and partition Scheme's

In the database level we have storage option.

 In this you can find partition functions and Partition sachem's.

Query to get  table related partitions and file groups

SELECT distinct
ps.name schemaname,pf.name functionname,fg.name file_name,t.name tablename
FROM sys.tables t inner join
 sys.partitions p on t.object_id=p.object_id
INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id
inner  JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id
join sys.indexes i on t.object_id = i.object_idjoin sys.data_spaces ds on i.data_space_id = ds.data_space_id
inner  join sys.partition_schemes ps on ps.data_space_id = ds.data_space_id
inner  join sys.partition_functions pf on ps.function_id = pf.function_id
order by t.name,Ps.name,fg.name