5

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
9

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

| improve this answer | |
  • 1
    Perfect! And to show tables in replication, just add this line under WHERE: AND ta.is_replicated = 1 – AndrewRalon Jun 23 '15 at 20:38
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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.