幸运的是，SQL Server管理工作室会给你一个提示，告诉你如何做到这一点。 这样做,

启动SQL Server跟踪并打开您正在进行的活动(过滤器) 如果您不是一个人，请通过您的登录ID，并设置应用程序名称 到Microsoft SQL Server Management Studio)，暂停跟踪并丢弃迄今为止记录的任何结果; 然后，右键单击一个表，并从弹出菜单中选择属性; 再次启动跟踪; 现在在SQL Server Management studio中选择左侧的存储属性项;

暂停跟踪，看看microsoft生成了什么TSQL。

在可能是最后一个查询中，您将看到以exec sp_executesql N'SELECT开头的语句

当您将执行的代码复制到visual studio时，您将注意到这段代码生成了微软工程师用于填充属性窗口的所有数据。

当你对该查询进行适度修改时，你会得到这样的结果:

SELECT
SCHEMA_NAME(tbl.schema_id)+'.'+tbl.name as [table], --> something I added
p.partition_number AS [PartitionNumber],
prv.value AS [RightBoundaryValue],
 fg.name AS [FileGroupName],
CAST(pf.boundary_value_on_right AS int) AS [RangeType],
CAST(p.rows AS float) AS [RowCount],
p.data_compression AS [DataCompression]
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2
INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int) AND p.index_id=idx.index_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON dds.partition_scheme_id = idx.data_space_id and dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.partition_schemes AS ps ON ps.data_space_id = idx.data_space_id
LEFT OUTER JOIN sys.partition_range_values AS prv ON prv.boundary_id = p.partition_number and prv.function_id = ps.function_id
LEFT OUTER JOIN sys.filegroups AS fg ON fg.data_space_id = dds.data_space_id or fg.data_space_id = idx.data_space_id
LEFT OUTER JOIN sys.partition_functions AS pf ON  pf.function_id = prv.function_id

现在的查询并不完美，你可以更新它来满足你可能遇到的其他问题，关键是，你可以使用微软的知识来得到你的大多数问题，通过执行你感兴趣的数据和跟踪使用分析器生成的TSQL。

我喜欢认为MS工程师知道SQL server是如何工作的，它会生成TSQL，适用于所有项目，你可以使用你正在使用的SSMS上的版本，所以它在以前、现在和未来的各种版本上都很好。

记住，不要只是照抄，要试着理解它，否则你可能会得到错误的答案。

沃尔特

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

    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

要在SQL Management Studio中获取该信息，右键单击数据库，然后选择Reports—> Standard Reports—> Disk Usage by Table。

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

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

我想分享对我有效的方法

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/