如何获取数据库中所有分区表的列表？

我认为更好的查询如下：

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

这着眼于识别分区方案的“正确”位置：sys.partition_schemes，它具有正确的基数（不需要distinct），它仅显示分区对象（不需要过滤where子句），它投射模式名称和分区方案名称。还要注意这个查询如何突出原始问题的一个缺陷：分区的不是表，而是索引（这包括索引 0 和 1，也就是堆和聚集索引）。一个表可以有多个索引，有些分区有些没有。

这个查询应该给你你想要的：

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目录视图提供了表和大多数索引的所有分区的列表。只需加入sys.tables即可获得表格。

所有表都至少有一个分区，因此如果您专门寻找分区表，那么您必须根据以下条件过滤此查询sys.partitions.partition_number <> 1（对于非分区表，partition_number始终等于 1）。

那么，如何结合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    

我在搜索这种类型的脚本时发现了这篇文章，并利用一些资源从 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

我希望他们有帮助。