Get All Table Names With Row Counts In SQL Server: One Query To Rule Them All

In this tutorial, you will learn how to get all table names with row counts in SQL Server Database. Before we start, if you need to download the Crystal Report developer for VS 2019, please go through the following link: Crystal Reports for Visual Studio 2019.

If you are looking for the row count of all tables in all databases (which was what I was looking for), then I found this combination of this and this to work. No idea whether it is optimal or not:

SET NOCOUNT ON
DECLARE @AllTables table (DbName sysname,SchemaName sysname, TableName sysname, RowsCount int )
DECLARE @SQL nvarchar(4000)
SET @SQL='SELECT ''?'' AS DbName, s.name AS SchemaName, t.name AS TableName, p.rows AS RowsCount FROM [?].sys.tables t INNER JOIN sys.schemas s ON t.schema_id=s.schema_id INNER JOIN [?].sys.partitions p ON p.OBJECT_ID = t.OBJECT_ID'

INSERT INTO @AllTables (DbName, SchemaName, TableName, RowsCount)
EXEC sp_msforeachdb @SQL


SET NOCOUNT OFF
SELECT DbName AS [Database Name],SchemaName AS [Schema Name],TableName AS [Table Name], SUM(RowsCount) AS [Total Rows Count]
FROM @AllTables
WHERE RowsCount > 0
GROUP BY DbName, SchemaName, TableName
ORDER BY DbName, SchemaName, TableName

SET NOCOUNT ON

DECLARE @AllTables table (DbName sysname,SchemaName sysname, TableName sysname, RowsCount int )

DECLARE @SQL nvarchar(4000)

SET @SQL='SELECT ''?'' AS DbName, s.name AS SchemaName, t.name AS TableName, p.rows AS RowsCount FROM [?].sys.tables t INNER JOIN sys.schemas s ON t.schema_id=s.schema_id INNER JOIN [?].sys.partitions p ON p.OBJECT_ID = t.OBJECT_ID'