How to find tables which has more than 1000 Partitions

[Former Member]

Hi All,

Is there any other way to find out the tables which has more than 1000 Partitions ?

Apart from SAP report RSDD_MSSQL_CUBEANALYZE. Because this report is not working for me as job getting cancel again and again with ABAP dump DBIF_DSQL2_SQL.I already check SAP Note 1309838, but itu2019s not applicable for us because we are on highest support package level SAP_BW 701 SP06.

Thanks,

Harshal

[clas_hortien · Accepted solution]

Hi,

yes, there is an easy way to find these table with more than 1000 partitions. There are none.

SQL Server has a hard limit of 1000 partitions per table, so there will be not table with more than 1000

partitions.

Regards

Clas

[Former Member]

If you are running SQL Server as your database platform run this query:

select o.object_id,o.name,p.Partition_count from sys.objects o

inner join

(select object_id,count(distinct partition_number) as Partition_count

from sys.partitions

group by object_id)

p

on o.object_id = p.object_id

where o.type = 'U'

order by p.Partition_count desc

[Former Member]

Thanks.