If you need to find out if a table has been partitioned in SQL Server, you can run a join against the sys.tables
, sys.indexes
, and sys.partition_schemes
views.
Example
Here’s an example to demonstrate.
SELECT
t.name AS [Table],
i.name AS [Index],
i.type_desc,
i.is_primary_key,
ps.name AS [Partition Scheme]
FROM sys.tables t
INNER JOIN sys.indexes i
ON t.object_id = i.object_id
AND i.type IN (0,1)
INNER JOIN sys.partition_schemes ps
ON i.data_space_id = ps.data_space_id
WHERE t.name = 'Movies';
Result:
+---------+------------------------------+-------------+------------------+-----------------------+ | Table | Index | type_desc | is_primary_key | Partition Scheme | |---------+------------------------------+-------------+------------------+-----------------------| | Movies | PK__Movies__4BD2941AD44D2FCF | CLUSTERED | 1 | MoviesPartitionScheme | +---------+------------------------------+-------------+------------------+-----------------------+
Here it is again using vertical output (so that you don’t have to scroll sideways):
Table | Movies Index | PK__Movies__4BD2941AD44D2FCF type_desc | CLUSTERED is_primary_key | 1 Partition Scheme | MoviesPartitionScheme
I’m only returning a small subset of the actual columns, but feel free to return whichever columns interest you.
Also, you can remove the last line to return all partitioned tables.
SELECT
t.name AS [Table],
i.name AS [Index],
i.type_desc,
i.is_primary_key,
ps.name AS [Partition Scheme]
FROM sys.tables t
INNER JOIN sys.indexes i
ON t.object_id = i.object_id
AND i.type IN (0,1)
INNER JOIN sys.partition_schemes ps
ON i.data_space_id = ps.data_space_id;
Result:
+---------+------------------------------+-------------+------------------+-----------------------+ | Table | Index | type_desc | is_primary_key | Partition Scheme | |---------+------------------------------+-------------+------------------+-----------------------| | Movies | PK__Movies__4BD2941AD44D2FCF | CLUSTERED | 1 | MoviesPartitionScheme | +---------+------------------------------+-------------+------------------+-----------------------+
In my case, there’s only one partitioned table in the database.
Just to be clear, this only returns tables that have explicitly been partitioned.
Return the Partitions
You can also query other views, such as sys.partitions
and sys.dm_db_partition_stats
to return a list of the partitions for each table.
Note that both of these views will also return tables that haven’t explicitly been explicitly partitioned (i.e. they have just one partition). In SQL Server, tables and views that haven’t been explicitly partitioned still do have a partition (partition_number
= 1).
Here’s an example of sys.dm_db_partition_stats
:
SELECT
partition_number,
row_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('Movies');
Result:
+--------------------+-------------+ | partition_number | row_count | |--------------------+-------------| | 1 | 0 | | 2 | 100 | | 3 | 3979 | | 4 | 0 | +--------------------+-------------+
Here’s an example where I return the partition info for a non-partitioned table.
SELECT
partition_number,
row_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('Cats');
Result:
+--------------------+-------------+ | partition_number | row_count | |--------------------+-------------| | 1 | 3 | +--------------------+-------------+
As mentioned, it still has one partition, and its partition_number
is 1
.