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

如图所示，这将返回正确的计数，而使用元数据表的方法将只返回估计值。

    CREATE PROCEDURE ListTableRowCounts 
    AS 
    BEGIN 
        SET NOCOUNT ON 

        CREATE TABLE #TableCounts
        ( 
            TableName VARCHAR(500), 
            CountOf INT 
        ) 

        INSERT #TableCounts
            EXEC sp_msForEachTable 
                'SELECT PARSENAME(''?'', 1), 
                COUNT(*) FROM ? WITH (NOLOCK)' 

        SELECT TableName , CountOf 
            FROM #TableCounts
            ORDER BY TableName 

        DROP TABLE #TableCounts
    END
    GO

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

以下是我对这个问题的看法。它包含所有模式，只列出带行的表。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;

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

USE DatabaseName
CREATE TABLE #counts
(
    table_name varchar(255),
    row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC
DROP TABLE #counts