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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.