以下是我对这个问题的看法。它包含所有模式，只列出带行的表。YMMV

select distinct schema_name(t.schema_id) as schema_name, t.name as 
table_name, p.[Rows]
from sys.tables as t
INNER JOIN sys.indexes as 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 p.[Rows] > 0
order by schema_name;

你可以试试这个:

SELECT  OBJECT_SCHEMA_NAME(ps.object_Id) AS [schemaname],
        OBJECT_NAME(ps.object_id) AS [tablename],
        row_count AS [rows]
FROM sys.dm_db_partition_stats ps
WHERE OBJECT_SCHEMA_NAME(ps.object_Id) <> 'sys' AND ps.index_id < 2
ORDER BY 
        OBJECT_SCHEMA_NAME(ps.object_Id),
        OBJECT_NAME(ps.object_id)

您可以复制，过去和执行这段代码，以获得所有表记录计数到一个表。注意:代码带有注释

create procedure RowCountsPro
as
begin
--drop the table if exist on each exicution
IF OBJECT_ID (N'dbo.RowCounts', N'U') IS NOT NULL 
DROP TABLE dbo.RowCounts;
-- creating new table
CREATE TABLE RowCounts 
( [TableName]            VARCHAR(150)
, [RowCount]               INT
, [Reserved]                 NVARCHAR(50)
, [Data]                        NVARCHAR(50)
, [Index_Size]               NVARCHAR(50)
, [UnUsed]                   NVARCHAR(50))
--inserting all records
INSERT INTO RowCounts([TableName], [RowCount],[Reserved],[Data],[Index_Size],[UnUsed])
--  "sp_MSforeachtable" System Procedure, 'sp_spaceused "?"' param to get records and resources used
EXEC sp_MSforeachtable 'sp_spaceused "?"' 
-- selecting data and returning a table of data
SELECT [TableName], [RowCount],[Reserved],[Data],[Index_Size],[UnUsed]
FROM RowCounts
ORDER BY [TableName]
end

我已经测试了这段代码，它在SQL Server 2014上运行良好。

首先想到的是使用sp_msForEachTable

exec sp_msforeachtable 'select count(*) from ?'

但是它没有列出表名，所以可以扩展到

exec sp_msforeachtable 'select parsename(''?'', 1),  count(*) from ?'

这里的问题是，如果数据库有超过100个表，你会得到以下错误消息:

查询已超过最大值 可以的结果集的数目 显示在结果网格中。只有 前100个结果集是 显示在网格中。

所以我最终使用表变量来存储结果

declare @stats table (n sysname, c int)
insert into @stats
    exec sp_msforeachtable 'select parsename(''?'', 1),  count(*) from ?'
select 
    * 
from @stats
order by c desc

这种方法使用字符串连接来动态生成包含所有表及其计数的语句，就像原始问题中给出的示例:

          SELECT COUNT(*) AS Count,'[dbo].[tbl1]' AS TableName FROM [dbo].[tbl1]
UNION ALL SELECT COUNT(*) AS Count,'[dbo].[tbl2]' AS TableName FROM [dbo].[tbl2]
UNION ALL SELECT...

最后用EXEC执行:

DECLARE @cmd VARCHAR(MAX)=STUFF(
                    (
                        SELECT 'UNION ALL SELECT COUNT(*) AS Count,''' 
                              + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) 
                              + ''' AS TableName FROM ' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME)
                        FROM INFORMATION_SCHEMA.TABLES AS t
                        WHERE TABLE_TYPE='BASE TABLE'
                        FOR XML PATH('')
                    ),1,10,'');
EXEC(@cmd);

我认为最短、最快、最简单的方法是:

SELECT
    object_name(object_id) AS [Table],
    SUM(row_count) AS [Count]
FROM
    sys.dm_db_partition_stats
WHERE
    --object_schema_name(object_id) = 'dbo' AND 
    index_id < 2
GROUP BY
    object_id