SQL: Find largest table in database

Question:

Who do I find the table with most rows for a given database in sql (SqlServer 2005)

Answer:

You can query this dynamic management view : dm_db_partition_stats

Very short

USE AdventureWorks2008R2;
GO
SELECT * FROM sys.dm_db_partition_stats;
GO

Here is an example

SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY st.row_count DESC

Here is one that takes the schema into account

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

found here