你可以试试这个:

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)

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

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

sp_MSForEachTable 'DECLARE @t AS VARCHAR(MAX); 
SELECT @t = CAST(COUNT(1) as VARCHAR(MAX)) 
+ CHAR(9) + CHAR(9) + ''?'' FROM ? ; PRINT @t'

输出:

从这个问题中: 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;

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

我想分享对我有效的方法

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sdmvPTNS.row_count) AS [RowCount]
FROM
      sys.objects AS sOBJ
      INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
            ON sOBJ.object_id = sdmvPTNS.object_id
WHERE 
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND sdmvPTNS.index_id < 2
GROUP BY
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO

数据库托管在Azure中，最终结果是:

来源:https://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/