To list all the tables that are partitioned you can use the sys.partitions view. However be aware that all tables and indexes in SQL Server contain at least one partition, whether or not they are explicitly partitioned.

If you were to do the following, you would get back every table

SELECT partition_number,rows,object_name(object_id)
FROM sys.partitions

So what can you do? Let’s take a look. First we are going to create a partitioned table in case you don’t have one so that you can get the same output as me.

CREATE TABLE SalesPartitioned(YearCol SMALLINT NOT NULL,OrderID INT NOT NULL, SomeData UNIQUEIDENTIFIER DEFAULT newsequentialid())
GO

We are going to insert some data into the table

INSERT SalesPartitioned (YearCol,OrderID)
SELECT 2007,number
FROM master..spt_values
WHERE type = 'P'
UNION ALL
SELECT 2008,number + 2048
FROM master..spt_values
WHERE type = 'P'
UNION ALL
SELECT 2009,number + 4096
FROM master..spt_values
WHERE type = 'P'

We will now add a primary key

ALTER TABLE dbo.SalesPartitioned ADD CONSTRAINT
	PK_Sales PRIMARY KEY NONCLUSTERED (YearCol,OrderID)

Now it is time to create our partition function. Here is how we will do it

CREATE PARTITION FUNCTION pfFiscalYear(SMALLINT)
AS RANGE RIGHT FOR VALUES(2007,2008,2009)

What that does is actually create 4 partitions, one for 2007, one for 2008, one for everything after 2008, and one for everything before 2006.

<=2006 = 2007 = 2008 >= 2009

You can verify this by using the function $partition

select 1 AS val,$partition.pfFiscalYear(1) AS partition	    UNION all
select 2006,$partition.pfFiscalYear(2006)	UNION all
select 2007,$partition.pfFiscalYear(2007)	UNION all
select 2008,$partition.pfFiscalYear(2008)	UNION all
select 2009,$partition.pfFiscalYear(2009)	UNION all
select 2010,$partition.pfFiscalYear(2010)	UNION all
select 3000,$partition.pfFiscalYear(3000)

And here is the output

val         partition
----------- -----------
1           1
2006        1
2007        2
2008        3
2009        4
2010        4
3000        4

Now that we have the partition function, we need a partition scheme. A partition scheme is used to map boundary values in partition functions to filegroups. You can have one filegroup for each year placed on a different spindle, this way you don’t have to wait for the disk if all partitions are on the same spindle. For the sake of simplicity we only have one filegroup. Here is how to create the partition scheme

CREATE PARTITION SCHEME psFiscalYear
AS PARTITION pfFiscalYear ALL TO ([PRIMARY])

Partition scheme ‘psFiscalYear’ has been created successfully. ‘PRIMARY’ is marked as the next used filegroup in partition scheme ‘psFiscalYear’.

Now we will add a clustered index and partition this on the YearCol column, the syntax looks like this

CREATE CLUSTERED INDEX IX_Sales ON SalesPartitioned(YearCol,OrderID)
ON psFiscalYear(YearCol)

Now it is time to list all the tables that are partitioned. Here is how you do it

SELECT partition_number,rows,object_name(object_id)
FROM sys.partitions s
WHERE EXISTS(SELECT NULL 
				FROM sys.partitions s2 
				WHERE s.object_id = s2.object_id 
				AND partition_number > 1
				AND s.index_id = s2.index_id)

Here is the output

partition_number   rows    TableName
1                   0   SalesPartitioned
2                   2048    SalesPartitioned
3                   2048    SalesPartitioned
4                   2048    SalesPartitioned

As you can see we used WHERE EXISTS, we checked that the object had a partition_number higher than two and we also matched on index_id since a table can have more than one index