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_idWHERE 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,TableNameDROP TABLE #RowCounts