How do I get a list of all the partitioned tables in my database?

Question

Which system tables/DMVs should I be looking at?

score 31 · Answer 1 · answered Mar 14 '12 at 19:11

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.

This is the right answer instead of the 1st one - taking into account if the table is ON partition scheme instead of filegroup — Oleg Dok, Feb 16 '16 at 12:44

Thomas Stringer · Accepted Answer · 2012-03-14T17:07:32.557

24

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

The 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).

edited Mar 14 '12 at 17:07

answered Mar 14 '12 at 15:52

Thomas Stringer

41,128
9
109
152

this returned 50K objects and something about it doesn't feel right to me. We are in the middle of a Peoplesoft Finance db upgrade from sql 2005 to 2008 R2 and it seems the newer version of people tools doesn't support partitioned tables. Is there any other way to identify the portioned tables? – RK Kuppala Mar 14 '12 at 16:32
1

@yogirk So sorry, typo there. See my edit. Instead of `partition_id` in your WHERE clause you need `partition_number`. My apologies. – Thomas Stringer Mar 14 '12 at 17:01
Thanks for the edit and I am glad there are few tables to tackle, just like I expected :) – RK Kuppala Mar 14 '12 at 17:10
@yogirk Have fun with that! – Thomas Stringer Mar 14 '12 at 17:20
4

There is an error here - table is still can be partitioned (using PF and PS) but having single partition. So for those tables the query returns wrong results – Oleg Dok Feb 16 '16 at 12:42
@OlegDok I wouldn't say this is error - for some cases this is OK to search only for tables with more than one partition. But yes, for original question this query can be considered as wrong – nahab Sep 06 '17 at 14:20

score 3 · Answer 3 · answered Jan 20 '14 at 14:01

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

score 0 · Answer 4 · answered Jan 14 '21 at 13:53

I found this article while searching for this type of script and worked from a few resources to create these from a SQL 2019 server.

-- List partitioned tables (excluding system tables)

SELECT DISTINCT so.name
FROM sys.partitions sp
       JOIN sys.objects so ON so.object_id = sp.object_id
       where name NOT LIKE 'sys%' and name NOT LIKE 'sqla%' and name NOT LIKE 'plan%' 
       and name NOT LIKE 'persistent%' and name NOT LIKE 'queue_messages%'
       and name NOT LIKE 'ifts%' and name NOT LIKE 'fulltext%'
       ORDER BY name


-- List partitioned tables and partition information (excluding system tables)

SELECT so.name
      ,[partition_id]
      ,sp.[object_id]
      ,[index_id]
      ,[partition_number]
      ,[hobt_id]
      ,[rows]
      ,[filestream_filegroup_id]
      ,[data_compression]
      ,[data_compression_desc]
  FROM sys.partitions sp
       JOIN sys.objects so ON so.object_id = sp.object_id
       where name NOT LIKE 'sys%' and name NOT LIKE 'sqla%' and name NOT LIKE 'plan%' 
       and name NOT LIKE 'persistent%' and name NOT LIKE 'queue_messages%'
       and name NOT LIKE 'ifts%' and name NOT LIKE 'fulltext%'
       ORDER BY name

I hope they are helpful.

How do I get a list of all the partitioned tables in my database?

4 Answers4