I recently needed to know which tables in my database were partitioned. I tried a bunch of queries and some got incredibly complex. I finally found one that I like:

select 
    object_schema_name(i.object_id) as [schema],
    t.name as [table_name],
    i.name as [index_name],
    s.name as [partition_scheme]
from sys.indexes i
    join sys.partition_schemes s on i.data_space_id = s.data_space_id
    join sys.tables t on i.object_id = t.object_id  
WHERE i.type = 1

This query makes an assumption that the partitions are on the Clustered Index. In it, I am showing the schema, the table name, the name of the clustered index, and the partition scheme.

The song for this post is Boxes by the Goo Goo Dolls.