快速读取总数据量的另一种方法：sys.partitions

[guguda2008]

在我上一篇博客中提到了通过系统表拼接SQL语句，读取数据库中所有表定制数据量的方法，在回帖中，沟沟同学提到了另一种方法：

Exec sp_MSforeachtable@precommand="Createtable
 ##(name sysname,rowsint,reservednvarchar(20),datanvarchar(20),index_sizenvarchar(20),unusednvarchar(20))",@command1="insertinto
 ##exec sp_spaceused'?'",@postcommand="select*from
 ##"droptable
 ##

下面就来分析一下这种方法。




这个语句是先创建全局临时表，再遍历所有表，

执行sp_spaceused，将返回的行数和占用空间插入临时表中，最后查询表并删除表。




可以知道读取数据量的关键部分在于sp_spaceused存储过程。

这个存储过程中的表行数是从哪来的呢？它是从sys.partitions系统表中的rows列中来的。




在联机丛书中，对rows列的描述是“此分区中的大约行数”，

那么这个“大约”是虾米意思呢？

其实就是未提交的行数也会在这个表中显示。

下面就以一个简单的例子来演示一下：




1.创建一个测试表，插入3行数据，和2行未提交的数据

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB  
GO  
CREATE TABLE TB(COL VARCHAR(50))  
INSERT INTO TB  
SELECT 'A' UNION ALL  
SELECT 'B' UNION ALL  
SELECT 'C'  
GO  
  
BEGIN TRAN  
  
INSERT INTO TB  
SELECT 'D' UNION ALL  
SELECT 'E'  
  
--ROLLBACK TRAN  

2.新开一个窗口，运行下面三句

SELECT * FROM TB WITH(NOLOCK)  
  
exec SP_SPACEUSED 'TB'  
  
SELECT ROWS  
FROM SYS.PARTITIONS  
where OBJECT_NAME(OBJECT_ID)='TB'  

可以看到，返回的行数都是5。
如果把第一句的WITH(NOLOCK)去掉，会读不出数量，因为第一个会话的事务没提交。




3.回到第一个窗口，运行注释里的ROLLBACK TRAN回滚事务，再运行新窗口的三个查询，可以看到数量又回到3了。




因此，对于想要快速看到表中大概数据量的需求，可以使用下面的语句：

SELECT OBJECT_NAME(OBJECT_ID),ROWS  
FROM SYS.PARTITIONS  
ORDER BY OBJECT_NAME(OBJECT_ID)  

对于只要看总数量，不需要太准确的同学来说，此语句足够了。

[a466455660]

在我上一篇博客中提到了通过系统表拼接SQL语句，读取数据库中所有表定制数据量的方法，在回帖中，沟沟同学提到了另一种方法：

[goupigougi]

谢谢分享。

[mqr5945mlm]

谢谢楼主分享

[AcHerat]

感谢鸭子的分享，收藏。

[playwarcraft]

鸭子你再这么投入，你会找不到女朋友，找不到老婆的，赶快回头是岸吧，阿弥陀佛。。。

[特别安静]

鸭子你再这么投入，你会找不到女朋友，找不到老婆的，赶快回头是岸吧，阿弥陀佛。。。

[mcl66666]

学习了，谢谢分享！

[Barton]

[Quote=引用 27 楼 的回复:]

学习了，谢谢分享！
[/Quote]

[zjh_c_s_d_n]

[Quote=引用 24 楼 的回复:]

[/Quote]
很好，很强大！

[zhazhuzhao]

不错，分析的好，做的例子也好，讲解到位，相当好！

[xiying12571]

感谢鸭子的分享，推荐一下！

[tskims]

谢谢分享。

[tuonioooo]

好的东西，分享了

[haitao]

SELECT OBJECT_NAME(OBJECT_ID),OBJECT_ID,ROWS,*
FROM SYS.PARTITIONS  
ORDER BY OBJECT_NAME(OBJECT_ID) 

很多表有几条相同的记录
分区表就有n+m条
嗯，取最大的，应该是需要的：

SELECT OBJECT_NAME(OBJECT_ID),max(ROWS)
FROM SYS.PARTITIONS  
group BY OBJECT_NAME(OBJECT_ID) 

[dengqiang2016]

感觉sql语句也是博大精深的，不能够完全的掌握，只能使用一点点常用的功能。

[qqcom371939341]

很有用

[liuxu5694]

抱着学习的心态看下来，发现sql有他强大之处

[静思长远]

sys.partitions

[zhazhuzhao]

呵呵，之前的是会取出sysindexes中的max(rowcunt)，现在看来可以把idexid范围缩小一下，速度可以更快点了。

[汤姆克鲁斯]

sp_spaceused 最快了

可能它返回的结果比较多，可以单独执行

SELECT   
  [rowCount] = SUM (  
   CASE  
    WHEN (index_id < 2) THEN row_count  
    ELSE 0  
   END  
   )  
 FROM sys.dm_db_partition_stats  
 WHERE object_id = object_id('tb');  

要比count(*) 快很多 而且和count 结果也是一致的。