查询数据库中所有表的数据量、有效数据量以及其它定制数据量

[guguda2008]

经常会遇到类似的需求，有时不只是要有效，也有可能要今年的，或其它什么样的数据

最基础的办法是手动一个一个写。比如：

SELECT COUNT(1) FROM A  
SELECT COUNT(1)  FROM B  
SELECT COUNT(1)  FROM C  

上面这种写法，在查询客户端里跑的时候，会返回N个查询结果，看起来比较麻烦，而且拖到下边会不知道这个结果是哪个表的。

我通常的写法，是在查询列中加一列常量，然后用UNION ALL将结果集合并为一个，就像下面的格式：

SELECT 'A' AS TB,COUNT(1) AS TOTAL FROM A UNION ALL  
SELECT 'B',COUNT(1) FROM B UNION ALL  
SELECT 'C',COUNT(1) FROM C 

这样会写会返回一个两列的结果集，列名和对应的数量都有。但这只是开始。


由于数据库中的表可能会添加、改名、删除，你的语句也就得跟着变，如果数据库开发人员比较无脑（大部分时候这个人就是你），语句变动的频率也会很快，所以懒惰的我想要一直用一个不变的语句查询。这时需要用到系统表了，有两个表可以用，一个是SYS.TABLES，一个是INFORMATION_SCHEMA.TABLES。前者返回当前库中所有用户表，后者返回所有用户表和视图，实际我们只需要里面的表名列来生成我们的查询语句。我一般使用前者。先来一个简单一点的

SELECT 'UNION ALL SELECT '''+NAME+''' AS TB,COUNT(1) AS TOTAL FROM ['+NAME+']'  
FROM SYS.TABLES 

如上，把查询结果全部复制到新的查询窗口里，再删掉第一个"UNION ALL "，就是生成好的查询语句了，你可以在任何时候用这两步来生成你需要的查询语句，不用管表变了多少。

但懒人还是觉得麻烦，因为要复制出来，还要删字符串，还要再运行一次。所以我会再加上几句，把这几步也省掉。

DECLARE @SQL VARCHAR(MAX)  
SELECT @SQL=ISNULL(@SQL+'  
UNION ALL ','')+'SELECT '''+NAME+''' AS TB,COUNT(1) AS TOTAL FROM ['+NAME+']'  
FROM SYS.TABLES  
EXEC (@SQL)  

一次运行就出结果，方便快捷。喜欢的同学可以存起来。但这也只是开始


-----------------------------------------------------木有分割线的文章不是好文章-------------------------------------------------------------------


在使用了系统表、字符串拼接、动态SQL执行后，我们可以不关注表名查询库中所有表的数据量了，但万恶的业务人员或万万恶的BOSS会提出更过分的要求，比如要有效的，要今年的。下面我就以这几个现在能想到的需求为例，扩展一下上面的语句，让它更强大：

首先我们假设库中有一部分表，区分有效和无效的状态位为STATE，1为有效，0为无效。另有一部分表中有CREATE_TIME列，存创建日期，又有一部分表中有INPUT_TIME，也是存创建日期，这两部分表有交集。按这种需求，写好的SQL语句如下：

DECLARE @SQL VARCHAR(MAX)  
SELECT @SQL=ISNULL(@SQL+'  
UNION ALL ','')+'SELECT '''+T1.NAME+''' AS TB,COUNT(1) AS TOTAL,'+  
CASE WHEN T2.NAME IS NOT NULL THEN 'SUM(CASE WHEN STATE=1 THEN 1 ELSE 0 END)' ELSE 'NULL' END+  
' AS STATE,'+  
CASE WHEN T3.NAME IS NOT NULL THEN 'SUM(CASE WHEN YEAR(CREATE_TIME)=YEAR(GETDATE()) THEN 1 ELSE 0 END)'  
ELSE 'NULL' END+  
' AS CREATE_TIME,'+  
CASE WHEN T4.NAME IS NOT NULL THEN 'SUM(CASE WHEN YEAR(INPUT_TIME)=YEAR(GETDATE()) THEN 1 ELSE 0 END)'  
ELSE 'NULL' END+  
' AS INPUT_TIME FROM ['+T1.NAME+']'  
FROM SYS.TABLES T1  
LEFT JOIN (  
SELECT OBJECT_NAME(OBJECT_ID) AS NAME FROM SYS.COLUMNS WHERE NAME='STATE'  
) T2 ON T1.NAME=T2.NAME  
LEFT JOIN (  
SELECT OBJECT_NAME(OBJECT_ID) AS NAME FROM SYS.COLUMNS WHERE NAME='CREATE_TIME'  
) T3 ON T1.NAME=T3.NAME  
LEFT JOIN (  
SELECT OBJECT_NAME(OBJECT_ID) AS NAME FROM SYS.COLUMNS WHERE NAME='INPUT_TIME'  
) T4 ON T1.NAME=T4.NAME  
EXEC (@SQL)  

结果自己运行就知道了，这个SQL唯一的BUG是，如果STATE,CREATE_TIME,INPUT_TIME不是对应的类型，可能会报转换错误，这可以通过关联SYS.TYPES表确认字段类型避免。但考虑到我已经声明了我是个懒人，所以决定不写了。


上面的SQL，已经基本体现出了动态查询的强大之处，你可以通过关联数据字典和拼接字符串，生成你想要的任意条件的查询语句，如果你在查询SYS.TABLES时加入了条件，那也就实现了查询特定部分表的功能。基本上，只要一次写好，后面查询的时候你都可以把语句复制进去以后枕着双手惬意的看着服务器忙活而不用再手忙脚乱的调试语句中的错误了。

BLOG地址：
http://blog.csdn.net/guguda2008/article/details/7718367

[woshixiaoge]

太牛了

[菩提树下小沙弥]

好难啊

[yugan34]

经典 学习了

[julovexue]

不错
呵呵

[叶子]

不错，支持一下！！

[dengqiang2016]

太好了，可以深刻的学习一下sql语句了，一直以来只会写一些简单的sql语句。

[zhazhuzhao]

dba的查询数据量的脚步：
无论多大的表，瞬间返回，不会对表产生io操作，2008版本。

select OBJECT_NAME(id) as tablename,MAX(rowcnt) as rownum from sys.sysindexes
where OBJECT_NAME(id) in ('test','tb')
group by id

[csdn_无名小辈]

好难啊啊

[ywp1984916]

膜拜。经典。
膜拜。经典。

[ccbphi]

收藏加实践

[tyj_130]

鼓掌 鼓掌

[godofadam]

号东西啊 精炼啊 赞一个

[Forrest23]

加个order排序就好了

[LAONINGA098]

感谢分享

[以学习为目的]

现在才发现这么好的帖子，收藏

[诺亚的木船]

引用 29 楼 youbl 的回复:

如果仅仅是获取所有表的行数，用下面的sql就可以了

-- 获取表的行数
SELECT a.id, b.[name], a.ROWS FROM sysindexes a, sys.tables b WHERE a.id = b.[object_id] AND a.indid <=1 ORDER BY b.[name]

太感谢了，最近要整理数据，有这个很方便统计。

[诺亚的木船]

回贴作个记号收藏~都很实用的。

[BiChangQing]

向楼主学习！果断收藏！

[Neo_whl]

钻研的精神可贵，赞

[---涛声依旧---]