TSQL : Find the largest table

A script to find the largest table in the current database.

SELECT  t.name, s.row_count
FROM sys.dm_db_partition_stats s
INNER JOIN sys.tables t
ON t.object_id = s.object_id
WHERE s.index_id < 2
ORDER BY s.row_count DESC

This works for SQL 2005 up ...

To do this in SQL 2000, you would have to use sp_spaceused and loop through all tables.