SQL Partitions

/** Table Name + Schema Name + Function Details. **/
SELECT OBJECT_NAME(SI.object_id) AS PartitionedTable
, DS.name AS PartitionScheme
, PF.name AS PartitionFunction
FROM sys.indexes AS SI
JOIN sys.data_spaces AS DS
ON DS.data_space_id = SI.data_space_id
JOIN sys.partition_schemes AS PS
ON PS.data_space_id = SI.data_space_id
JOIN sys.partition_functions AS PF
ON PF.function_id = PS.function_id
WHERE DS.type = 'PS'
AND OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U'
AND SI.index_id IN(0,1);

GO

/** Function Insight Details. **/
SELECT
PF.function_id
, PF.name
, PF.fanout AS NumPartitions
, CASE WHEN PF.boundary_value_on_right = 0
THEN 'LEFT' ELSE 'RIGHT' END AS RangeType
, PP.parameter_id
, CASE WHEN PP.system_type_id = PP.user_type_id
THEN T1.name ELSE T2.name END AS ParameterDataType
, PRV.boundary_id
, PRV.value
, CASE WHEN PF.boundary_value_on_right = 0
THEN PRV.boundary_id ELSE PRV.boundary_id + 1 END AS PartitionNumber
FROM sys.partition_functions AS PF
JOIN sys.partition_parameters AS PP
ON PF.function_id = PP.function_id
JOIN sys.types AS T1
ON T1.system_type_id = PP.system_type_id
JOIN sys.types AS T2
ON T2.user_type_id= PP.user_type_id
JOIN sys.partition_range_values AS PRV
ON PP.function_id = PRV.function_id
AND PP.parameter_id = PRV.parameter_id
order by PF.name,PRV.Value

go

/** switch out the parition **/

alter table switch partition @partitionnr to partition @partitionnr

go

/** Adding a Partition on the existing Table **/


CREATE CLUSTERED INDEX [ix_DataDt_tab1] ON [dbo].[tab1]([DataDt] ASC)ON [SCHEME_Name]([PartitionKey])
CREATE NONCLUSTERED INDEX [ix_LoanId_tab1] ON [dbo].[tab1]([LoanId] ASC) ON [SCHEME_Name]([PartitionKey])