主页 / dba / 问题 / 25777
Accepted
jrara
Asked: 2012-10-11 22:46:56 +0800 CST

如何检查事实表中是否有正在使用的分区？

  • 772

我将首先了解一个包含许多事实表的大型数据仓库。如何轻松检查这些事实表中是否使用了分区和分区函数？

sql-server sql-server-2008

2 个回答

  1. Best Answer

    下面的脚本将为您提供一个表列表和分区数，其中有多个分区的表：

    select schema_name(schema_id), name, COUNT(name) from sys.tables t
inner join sys.partitions p on t.object_id = p.object_id
group by schema_name(schema_id), name
having COUNT(name) > 1
    • 2 
  2. select t.name as table_name,
    i.name as index_name,
    ds.type_desc,
    ps.name as partition_scheme_name
from sys.tables t
join sys.indexes i on t.object_id = i.object_id
join sys.data_spaces ds on i.data_space_id = ds.data_space_id
left join sys.partition_schemes ps on ps.data_space_id = ds.data_space_id
    • 1

相关问题