Parition Info (which tables are partitioned?)

--
-- ref: http://www.sqlservercurry.com/2009/08/find-partition-information-in-sql.html

SELECT DISTINCT
    OBJECT_NAME(part.object_id) as TableName
    ,psch.name as PartitionScheme
    ,idx.name as IndexName
    ,pfun.name as FunctionName
    ,idx.type_desc as IndexType
    --,part.object_id as ObjectID
    FROM    sys.partitions part    INNER JOIN sys.indexes idx
            ON part.[object_id] = idx.[object_id] and part.index_id = idx.index_id
            INNER JOIN sys.data_spaces dsp
            ON idx.data_space_id = dsp.data_space_id
            INNER JOIN sys.partition_schemes psch
            ON dsp.data_space_id = psch.data_space_id
            INNER JOIN sys.partition_functions pfun
            ON psch.function_id = pfun.function_id
    --WHERE    idx.is_primary_key=1
    ORDER BY 1,2