SQL: Get All Table Names And Their Row Counts In A DB

In this tutorial, you will learn how to get all table names and their row counts in a SQL Database. Before we get started, 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 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'