SQL SERVER – Find Row Count in Table – Find Largest Table in Database

Here's the Script :

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


Above query will return following resultset.

You can see in resultset that tablename is prefixed with Schema name and numbers of the rows are listed in the table.

If you are using earlier version of SQL Server 2000 you may want to run following command to update the pages and its statistics.

DBCC updateusage(adventureworks)

If you have better query to find out table row count, please share here.