Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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
share|improve this answer
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
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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