SQL Server – Query Row Count for All Tables

sql serverssms

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

Best Answer

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

Related Question