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

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

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

          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);

这个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

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

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

Shnugo的答案是唯一一个工作在Azure的Externa表。(1) Azure SQL根本不支持sp_MSforeachtable;External表的partitions总是0。

如果您使用MySQL >4。X你可以用这个:

select TABLE_NAME, TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA="test";

请记住，对于某些存储引擎，TABLE_ROWS只是一个近似值。