Find Row Count in Table – Find Largest Table in Database

This script will gives row number for every table in database.

USE AdventureWorks
GO

 
----
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
GO

---

select sc.name +'.'+ ta.name

,sum(pa.rows) -- Approximate value

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

GO


---- With Columns and Size

CREATE TABLE #temp (

table_name sysname ,

row_count INT,

reserved_size VARCHAR(50),

data_size VARCHAR(50),

index_size VARCHAR(50),

unused_size VARCHAR(50))

SET NOCOUNT ON

INSERT #temp

EXEC sp_msforeachtable 'sp_spaceused ''?'''

SELECT a.table_name,

a.row_count,

COUNT(*) AS col_count,

a.data_size

FROM #temp a

INNER JOIN information_schema.columns b

ON a.table_name collate database_default

= b.table_name collate database_default

GROUP BY a.table_name, a.row_count, a.data_size

ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC

DROP TABLE #temp