Count total rows of all tables in a database SQL Server

Question

I would like to count the total rows of all tables of a SQL Server database, does anyone knows how can I do that query?

I will use this information in a job to transfer information between layers of a data lake.

Answer 1

You can take a glance to the following article;

Different approaches of counting number of rows in a table

This is my favorite one;

 SELECT SCHEMA_NAME(t.[schema_id]) AS [table_schema]
          ,OBJECT_NAME(p.[object_id]) AS [table_name]
          ,SUM(p.[rows]) AS [row_count]
    FROM [sys].[partitions] p
    INNER JOIN [sys].[tables] t ON p.[object_id] = t.[object_id]
    WHERE p.[index_id] < 2
    GROUP BY p.[object_id]
        ,t.[schema_id]
    ORDER BY 1, 2 ASC

This one find out total number of the SQL Database

SELECT 
      SUM(p.[rows]) AS [row_count]
FROM [sys].[partitions] p
INNER JOIN [sys].[tables] t ON p.[object_id] = t.[object_id]
WHERE p.[index_id] < 2

Answer 2

Used the following query to count all the rows of all tables of a database.

SELECT SUM(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