select T.object_id, T.name, I.indid, I.rows 
  from Sys.tables T 
  left join Sys.sysindexes I 
    on (I.id = T.object_id and (indid =1 or indid =0 ))
 where T.type='U'

这里indid=1表示群集索引，而indid=0表示堆索引

首先想到的是使用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);

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

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上运行良好。

这个sql脚本给出了所选数据库中每个表的模式、表名和行数:

SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
-- WHERE [Tables].name = N'name of the table'
GROUP BY SCHEMA_NAME(schema_id), [Tables].name
order by [TotalRowCount] desc

裁判:https://blog.sqlauthority.com/2017/05/24/sql-server-find-row-count-every-table-database-efficiently/

另一种表达方式:

SELECT  o.NAME TABLENAME,
  i.rowcnt 
FROM sysindexes AS i
  INNER JOIN sysobjects AS o ON i.id = o.id 
WHERE i.indid < 2  AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
ORDER BY i.rowcnt desc

从这个问题中: https://dba.stackexchange.com/questions/114958/list-all-tables-from-all-user-databases/230411#230411

我在@Aaron Bertrand提供的答案中添加了记录计数，该答案列出了所有数据库和所有表。

DECLARE @src NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @sql = N'', @src = N' UNION ALL 
SELECT ''$d'' as ''database'', 
    s.name COLLATE SQL_Latin1_General_CP1_CI_AI as ''schema'',
    t.name COLLATE SQL_Latin1_General_CP1_CI_AI as ''table'' ,
    ind.rows as record_count
  FROM [$d].sys.schemas AS s
  INNER JOIN [$d].sys.tables AS t ON s.[schema_id] = t.[schema_id]
  INNER JOIN [$d].sys.sysindexes AS ind ON t.[object_id] = ind.[id]
  where ind.indid < 2';

SELECT @sql = @sql + REPLACE(@src, '$d', name)
  FROM sys.databases
  WHERE database_id > 4
    AND [state] = 0
    AND HAS_DBACCESS(name) = 1;

SET @sql = STUFF(@sql, 1, 10, CHAR(13) + CHAR(10));

PRINT @sql;
--EXEC sys.sp_executesql @sql;