How to check if there is partitions in use in fact tables?

Question

I'm going to take a first look of a large data warehouse with many fact tables. How can I easily check if there is partitions and partition functions in use in these fact tables?

Answer 1

The below script will give you a list of tables, and count of partitions, where there are tables with multiple partitions:

select schema_name(schema_id), name, COUNT(name) from sys.tables t
inner join sys.partitions p on t.object_id = p.object_id
group by schema_name(schema_id), name
having COUNT(name) > 1

Answer 2

select t.name as table_name,
    i.name as index_name,
    ds.type_desc,
    ps.name as partition_scheme_name
from sys.tables t
join sys.indexes i on t.object_id = i.object_id
join sys.data_spaces ds on i.data_space_id = ds.data_space_id
left join sys.partition_schemes ps on ps.data_space_id = ds.data_space_id