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.