Como obtenho uma lista de todas as tabelas particionadas no meu banco de dados?

Acho que uma consulta melhor é a seguinte:

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

Isso examina o local 'apropriado' para identificar o esquema de partição: sys.partition_schemes, tem a cardinalidade correta (sem necessidade de distinct), mostra apenas o objeto particionado (sem necessidade de uma wherecláusula de filtragem), projeta o nome do esquema e o nome do esquema de partição. Observe também como essa consulta destaca uma falha na pergunta original: não são as tabelas que são particionadas, mas os índices (e isso inclui os índices 0 e 1, também conhecidos como heap e índice clusterizado). Uma tabela pode ter vários índices, alguns particionados, outros não.

Esta consulta deve fornecer o que você deseja:

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

A sys.partitionsexibição do catálogo fornece uma lista de todas as partições para tabelas e a maioria dos índices. Apenas JUNTE-se a isso sys.tablespara obter as tabelas.

Todas as tabelas têm pelo menos uma partição, portanto, se você estiver procurando especificamente por tabelas particionadas, precisará filtrar essa consulta com base em sys.partitions.partition_number <> 1(para tabelas não particionadas, partition_numberé sempre igual a 1).

Bem, então que tal combinar os 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    

Encontrei este artigo enquanto procurava por esse tipo de script e trabalhei com alguns recursos para criá-los em um servidor SQL 2019.

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

Espero que sejam úteis.