查看当前库中哪些表是分区表

[好记忆不如烂笔头abc]

查看当前库中哪些表是分区表，sql语句怎么写？谢谢！

[打一壶酱油]

if exists (select 1 from sys.procedures where name = 'sp_show_partition_range')
   drop procedure dbo.sp_show_partition_range
go

--------------------------------------------------------------------------------
-- author : p.c.w.l
-- source : www.sqlstudy.com
-- create : 2008-01-01
-- descr  : view partition range by 'partition table' or 'partition function'
--------------------------------------------------------------------------------

create procedure dbo.sp_show_partition_range
(
   @partition_table    nvarchar(255) = null
  ,@partition_function nvarchar(255) = null
)
as
begin
   set nocount on

   declare @function_id int
       set @function_id = null

   -- get @function_id base on @partition_table
   if len(@partition_table) > 0 begin
      select @function_id = s.function_id
        from sys.indexes i
                inner join sys.partition_schemes s
          on i.data_space_id = s.data_space_id
       where i.index_id < 2
         and i.object_id = object_id(@partition_table)
         
      if @function_id is null
         return 1         
   end

   -- get @function_id base on @partition_function
   if len(@partition_function) > 0 begin
      select @function_id = function_id
        from sys.partition_functions
       where name = @partition_function
       
      if @function_id is null
         return 1        
    end
   

   -- get partition range
   select partition_function = f.name
         ,t.partition
         ,t.minval
         ,value = case when f.boundary_value_on_right=1 then '<= val <' else '< val <=' end
         ,t.maxval
     from (
           select h.function_id
                 ,partition = h.boundary_id
                 ,minval    = l.value
                 ,maxval    = h.value
             from sys.partition_range_values h
                     left join sys.partition_range_values l
               on h.function_id = l.function_id and h.boundary_id = l.boundary_id + 1

           union all

           select function_id
                 ,partition = max(boundary_id) + 1
                 ,minval    = max(value)
                 ,maxval    = null
             from sys.partition_range_values
            group by function_id
          ) t
              inner join sys.partition_functions f
         on t.function_id = f.function_id
      where f.function_id = @function_id
         or @function_id is null
      order by 1, 2
end
go
 

[好记忆不如烂笔头abc]

显示的结果是表名吗？貌似是分区的范围？

[打一壶酱油]

[Quote=引用 2 楼 jycjyc 的回复:]
显示的结果是表名吗？貌似是分区的范围？
[/Quote]
关联一下

sys.tables
或
sys.objects

就行了

[好记忆不如烂笔头abc]

查看哪个字段呀？

[好记忆不如烂笔头abc]

版主来帮帮忙啊

[SQL77]

[Quote=引用 5 楼 jycjyc 的回复:]
版主来帮帮忙啊
[/Quote]
select * from sys.partitions

partition_id
bigint
分区的 ID。在数据库中是唯一的。

object_id
int
此分区所属的对象的 ID。每个表或视图都至少包含一个分区。

index_id
int
此分区所属的对象内的索引的 ID。

partition_number
int
所属索引或堆中的从 1 开始的分区号。对于未分区的表和索引，此列的值为 1。

hobt_id
bigint
包含此分区的行的数据堆或 B 树的 ID。

rows
bigint
此分区中的大约行数。

[好记忆不如烂笔头abc]

搞定：
select b.name from sys.partitions a,sys.tables b where a.object_id=b.object_id and a.partition_number>1 group by b.name

[--小F--]

[Quote=引用 7 楼 jycjyc 的回复:]
搞定：
select b.name from sys.partitions a,sys.tables b where a.object_id=b.object_id and a.partition_number>1 group by b.name
[/Quote]
不错