查每一张表的记录数（按记录数排序）：

SELECT
t.NAME AS 表名,
SUM(p.rows) AS 记录数
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE
t.NAME NOT LIKE 'dt%'
AND i.OBJECT_ID > 255
AND i.index_id <= 1
GROUP BY
t.NAME
ORDER BY 记录数 DESC

查总记录数（所有表）：

select sum(RowCounts) 总记录数 from(
SELECT
t.NAME AS TableName,
SUM(p.rows) AS RowCounts
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE
t.NAME NOT LIKE 'dt%'
AND i.OBJECT_ID > 255
AND i.index_id <= 1
GROUP BY
t.NAME
) tt