Checking for Partitions

Return all partition table in the database.

SELECT 
	s.name As SchemaName, t.name As TableName
FROM 
	sys.tables t
	Inner Join sys.schemas s On t.schema_id = s.schema_id
	Inner Join sys.partitions p on p.object_id = t.object_id
WHERE
	p.index_id In (0, 1)
GROUP BY 
	s.name, t.name
HAVING
	Count(*) > 1
ORDER BY 
	s.name, t.name;

The following query returns one or more rows if the table PartitionTable is partitioned. If the table is not partitioned, no rows are returned.

SELECT *   
 FROM sys.tables AS t   
 JOIN sys.indexes AS i   
     ON t.[object_id] = i.[object_id]   
     AND i.[type] IN (0,1)   
 JOIN sys.partition_schemes ps   
     ON i.data_space_id = ps.data_space_id   
 WHERE t.name = 'PartitionTable';   
 GO  

The following query returns the boundary values for each partition in the PartitionTable table.

SELECT 
     t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id, i.data_space_id, f.function_id, f.type_desc, r.boundary_id, r.value AS BoundaryValue   
 FROM 
     sys.tables AS t  
     JOIN sys.indexes AS i ON t.object_id = i.object_id  
     JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id   
     JOIN  sys.partition_schemes AS s ON i.data_space_id = s.data_space_id  
     JOIN sys.partition_functions AS f ON s.function_id = f.function_id  
     LEFT JOIN sys.partition_range_values AS r ON f.function_id = r.function_id and r.boundary_id = p.partition_number  
 WHERE 
     t.name = 'PartitionTable' AND i.type <= 1  
 ORDER BY 
     p.partition_number;

The following query returns the name of the partitioning column for table. PartitionTable.

SELECT   
      t.[object_id] AS ObjectID   
      , t.name AS TableName   
      , ic.column_id AS PartitioningColumnID   
      , c.name AS PartitioningColumnName   
  FROM 
      sys.tables AS t   
      JOIN sys.indexes AS i ON t.[object_id] = i.[object_id] AND i.[type] <= 1 -- clustered index or a heap              JOIN sys.partition_schemes AS ps ON ps.data_space_id = i.data_space_id              JOIN sys.index_columns AS ic ON ic.[object_id] = i.[object_id] AND ic.index_id = i.index_id AND ic.partition_ordinal >= 1 -- because 0 = non-partitioning column   
      JOIN sys.columns AS c ON t.[object_id] = c.[object_id] AND ic.column_id = c.column_id   
  WHERE 
      t.name = 'PartitionTable' 

SELECT ps.name,pf.name,boundary_id,value
FROM sys.partition_schemes ps
INNER JOIN sys.partition_functions pf ON pf.function_id=ps.function_id
INNER JOIN sys.partition_range_values prf ON pf.function_id=prf.function_id
where ps.name = 'psXXXXX' 

SELECT 
      p.partition_number AS PartitionNumber,
      f.name AS PartitionFilegroup, 
      p.rows AS NumberOfRows 
  FROM 
      sys.partitions p
      JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
      JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id
  WHERE 
      OBJECT_NAME(OBJECT_ID) = 'tblXXXXX'

Return all partition information for the database.

SELECT
	 OBJECT_SCHEMA_NAME(pstats.object_id) AS SchemaName
	,OBJECT_NAME(pstats.object_id) AS TableName
	,ps.name AS PartitionSchemeName
	,ds.name AS PartitionFilegroupName
	,pf.name AS PartitionFunctionName
	,CASE pf.boundary_value_on_right WHEN 0 THEN 'Range Left' ELSE 'Range Right' END AS PartitionFunctionRange
	,CASE pf.boundary_value_on_right WHEN 0 THEN 'Upper Boundary' ELSE 'Lower Boundary' END AS PartitionBoundary
	,prv.value AS PartitionBoundaryValue
	,c.name AS PartitionKey
	,CASE 
		WHEN pf.boundary_value_on_right = 0 
		THEN c.name + ' > ' + CAST(ISNULL(LAG(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100)) + ' and ' + c.name + ' <= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100)) 
		ELSE c.name + ' >= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100))  + ' and ' + c.name + ' < ' + CAST(ISNULL(LEAD(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100))
	END AS PartitionRange
	,pstats.partition_number AS PartitionNumber
	,pstats.row_count AS PartitionRowCount
	,p.data_compression_desc AS DataCompression
FROM 
	sys.dm_db_partition_stats AS pstats
	INNER JOIN sys.partitions AS p ON pstats.partition_id = p.partition_id
	INNER JOIN sys.destination_data_spaces AS dds ON pstats.partition_number = dds.destination_id
	INNER JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
	INNER JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
	INNER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
	INNER JOIN sys.indexes AS i ON pstats.object_id = i.object_id AND pstats.index_id = i.index_id AND dds.partition_scheme_id = i.data_space_id AND i.type <= 1 /* Heap or Clustered Index */
	INNER JOIN sys.index_columns AS ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.partition_ordinal > 0
	INNER JOIN sys.columns AS c ON pstats.object_id = c.object_id AND ic.column_id = c.column_id
	LEFT JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id AND pstats.partition_number = (CASE pf.boundary_value_on_right WHEN 0 THEN prv.boundary_id ELSE (prv.boundary_id+1) END)
ORDER BY 
	TableName, PartitionNumber;

Sources:

https://docs.microsoft.com/en-us/sql/relational-databases/partitions/create-partitioned-tables-and-indexes?view=sql-server-2017

https://www.cathrinewilhelmsen.net/2015/04/12/table-partitioning-in-sql-server/