Query Row Count for All Tables

Question

I need help creating an MSSQL query to pass in every table name to the query below, which returns the number of rows.

This is how I get the row count for a single table:

SELECT Total_Rows = SUM(st.row_count)
FROM sys.dm_db_partition_stats st
WHERE object_name(object_id) = 'TABLE_NAME' AND (index_id < 2)

What is the way to loop this query on all tables, returning an output of each table's name and row count?

Example output:

TableName     RowCount
---------------------
FirstTable    109
SecondTable   2195
ThirdTable    0

Answer 1

You can simply use this :

SELECT sc.name +'.'+ ta.name TableName
 ,SUM(pa.rows) RowCnt
 FROM sys.tables ta
 INNER JOIN sys.partitions pa
 ON pa.OBJECT_ID = ta.OBJECT_ID
 INNER JOIN sys.schemas sc
 ON ta.schema_id = sc.schema_id
 WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
 GROUP BY sc.name,ta.name
 ORDER BY SUM(pa.rows) DESC

Various other methods include:here

1. sys.partitions Catalog View

2. sys.dm_db_partition_stats Dynamic Management View (DMV)

3. sp_MSforeachtable System Stored Procedure

4. COALESCE() Function

Answer 2

Drop the WHERE object_name(object_id) = 'xxx' and instead GROUP BY your object name.

Also, join back to sys.objects to get those objects of type 'U'.

SELECT 
  OBJECT_NAME(st.object_id), 
  Total_Rows = SUM(st.row_count)
FROM sys.dm_db_partition_stats st
INNER JOIN sys.objects AS o 
  ON o.object_id = st.object_id
WHERE (index_id < 2)
  AND o.type = 'U'
GROUP BY st.object_id;