How do I get a list of all the partitioned tables in my database?

How do I get a list of all the partitioned tables in my database?

Which system tables/DMVs should I be looking at?

Methinks a better query is as follows:

select object_schema_name(i.object_id) as [schema],
    object_name(i.object_id) as [object],
    i.name as [index],
    s.name as [partition_scheme]
    from sys.indexes i
    join sys.partition_schemes s on i.data_space_id = s.data_space_id

This looks at the 'proper' place to identify the partition scheme: sys.partition_schemes, it has the right cardinality (no need for distinct), it shows only partitioned object (no need for a filtering where clause), it projects the schema name and partition scheme name. Note also how this query highlights a flaw on the original question: it is not tables that are partitioned, but indexes (and this includes index 0 and 1, aka. heap and clustered index). A table can have multiple indexes, some partitioned some not.

StackExchange Database Administrators Q#14996, answer score: 35