如果你使用的是SQL Server 2005及以上版本，你也可以使用这个:

SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    p.[Rows],
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes 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
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY 
    object_name(i.object_id) 

在我看来，它比sp_msforeachtable输出更容易处理。

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

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

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

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

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

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

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

    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