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?
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
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