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.