How do I get a list of all the partitioned tables in my database?
Which system tables/DMVs should I be looking at?
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 only takes a minute to sign up.Sign up to join this community
This query should give you what you want:
select distinct t.name from sys.partitions p inner join sys.tables t on p.object_id = t.object_id where p.partition_number <> 1
sys.partitions catalog view gives a list of all partitions for tables and most indexes. Just JOIN that with
sys.tables to get the tables.
All tables have at least one partition, so if you are looking specifically for partitioned tables, then you'll have to filter this query based off of
sys.partitions.partition_number <> 1 (for non-partitioned tables, the
partition_number is always equal to 1).
Methinks a better query is as follows:
select object_schema_name(i.object_id) as [schema], object_name(i.object_id) as [object], i.name as [index], s.name as [partition_scheme] from sys.indexes i join sys.partition_schemes s on i.data_space_id = s.data_space_id
This looks at the 'proper' place to identify the partition scheme:
sys.partition_schemes, it has the right cardinality (no need for
distinct), it shows only partitioned object (no need for a filtering
where clause), it projects the schema name and partition scheme name. Note also how this query highlights a flaw on the original question: it is not tables that are partitioned, but indexes (and this includes index 0 and 1, aka. heap and clustered index). A table can have multiple indexes, some partitioned some not.
Well, then how about combining the 2:
select object_schema_name(i.object_id) as [schema], object_name(i.object_id) as [object_name], t.name as [table_name], i.name as [index_name], s.name as [partition_scheme] from sys.indexes i join sys.partition_schemes s on i.data_space_id = s.data_space_id join sys.tables t on i.object_id = t.object_id