How do I get a list of SQL Server tables and their row counts?

list-sqlserver-table-row-count.sql

-- method 1
SET NOCOUNT ON 
 
DECLARE @SQL VARCHAR(255) 
SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')' 
EXEC(@SQL) 
 
CREATE TABLE #foo (
  tablename VARCHAR(255), 
  rc INT 
) 
 
INSERT #foo
EXEC sp_msForEachTable 'SELECT PARSENAME(''?'', 1), COUNT(*) FROM ?'
 
SELECT tablename, rc 
FROM #foo 
ORDER BY rc DESC 

DROP TABLE #foo
-- method 2
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