1、以数据库text为例:
USE text
go
SELECT A.NAME,MaxRows = MAX(B.rows)
FROM sys.tables A
INNER JOIN sys.partitions B ON A.object_id = B.object_id
GROUP BY A.name
ORDER BY MAX(B.rows) DESC - -按数据行数的降序进行排序显示
2、显示所有空表
USE text
go
SELECT A.NAME,MaxRows = MAX(B.rows)
FROM sys.tables A
INNER JOIN sys.partitions B ON A.object_id = B.object_id
GROUP BY A.name
HAVING MAX(B.rows) = 0
3、显示所有非空表
USE text
go
SELECT A.NAME,MaxRows = MAX(B.rows)
FROM sys.tables A
INNER JOIN sys.partitions B ON A.object_id = B.object_id
GROUP BY A.name
HAVING MAX(B.rows) > 0