Menu Close

TSQL – Show all tables with rows

If you would like to see the row count of each table in a database and you don’t want to go through every single table here is how you do it:

SELECT o.name AS [Table Name]
, ddps.row_count AS [Row Count]

FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id

WHERE i.index_id < 2 AND o.is_ms_shipped = 0 ORDER BY row_count DESC, o.name

Below you can find an alternative method which takes longer to execute.

Source: http://www.sqlmatters.com/Articles/Listing all tables in a database and their row counts and sizes.aspx

CREATE TABLE #RowCounts(NumberOfRows BIGINT,TableName VARCHAR(128))

EXEC sp_MSForEachTable ‘INSERT INTO #RowCounts
SELECT COUNT_BIG(*) AS NumberOfRows,
”?” as TableName FROM ?’

SELECT TableName,NumberOfRows
FROM #RowCounts
ORDER BY NumberOfRows DESC,TableName

DROP TABLE #RowCounts