SQL统计哪些表使用分区表
来源:互联网 发布:mac开机有个客人用户 编辑:程序博客网 时间:2024/04/26 12:58
http://topic.csdn.net/u/20090420/17/d4398b30-1bdf-4d31-984c-87c8d7e1aebf.html
SELECT OBJECT_NAME(p.object_id) AS ObjectName,
i.name AS IndexName,
p.index_id AS IndexID,
ds.name AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
prv_left.value AS LowerBoundaryValue,
prv_right.value AS UpperBoundaryValue,
CASE pf.boundary_value_on_right
WHEN 1 THEN 'RIGHT'
ELSE 'LEFT' END AS Range,
p.rows AS Rows
FROM sys.partitions AS p
JOIN sys.indexes AS i
ON i.object_id = p.object_id
AND i.index_id = p.index_id
JOIN sys.data_spaces AS ds
ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS ps
ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions AS pf
ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces AS dds2
ON dds2.partition_scheme_id = ps.data_space_id
AND dds2.destination_id = p.partition_number
JOIN sys.filegroups AS fg
ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left
ON ps.function_id = prv_left.function_id
AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right
ON ps.function_id = prv_right.function_id
AND prv_right.boundary_id = p.partition_number
WHERE
OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
UNION ALL
--non-partitioned table/indexes
SELECT
OBJECT_NAME(p.object_id) AS ObjectName,
i.name AS IndexName,
p.index_id AS IndexID,
NULL AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
NULL AS LowerBoundaryValue,
NULL AS UpperBoundaryValue,
NULL AS Boundary,
p.rows AS Rows
FROM sys.partitions AS p
JOIN sys.indexes AS i
ON i.object_id = p.object_id
AND i.index_id = p.index_id
JOIN sys.data_spaces AS ds
ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups AS fg
ON fg.data_space_id = i.data_space_id
WHERE
OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
ORDER BY
ObjectName,
IndexID,
PartitionNumber;
- SQL统计哪些表使用分区表
- SQL统计哪些表使用分区表
- SQL统计哪些表使用分区表
- SQL SERVER中的分区表使用
- sql 分区表
- 分区表统计信息用法
- SQL大表转为分区表实例
- SQL大表转为分区表实例 收藏
- SQL Server大表转为分区表实例
- SQL大表转为分区表实例
- 怎么在ArcSDE中使用SQL Server分区表
- SQL统计函数使用实例
- oracle分区表常用统计信息
- SQL Server到底需要使用哪些端口
- SQL Server到底需要使用哪些端口
- SQL Server到底需要使用哪些端口
- SQL Server到底使用哪些端口
- 使用Dbms_Redefinition转换普通表-》分区表
- sqlsever 数据库收缩处理
- Building Integrated SQL Server Business Intelligence (BI) Solutions with Microsoft Office 2007
- MIDI 音乐不出声的原因
- Windows Server 2008 R2 beta Evaluation SN
- JavaScript高级---自定义对象
- SQL统计哪些表使用分区表
- 人比事更烦心
- 网页对话框showModalDialog
- 接到前前老板给我推荐工作的电话
- basepage
- 关于inline 导致链接失败!
- 预防肝癌的最佳美食
- C++类类型的转换
- Handling workloads on SQL Server 2008 with Resource Governor