SQL SERVER – Find the Largest Table name and its Number of Rows

SQL SERVER – Find the Largest Table name and its Number of Rows

--select Database

Use AdventureWorks

GO

-- Find largest Table

SELECT  t.name Table_Name ,SUM(p.rows) Row_No

FROM sys.tables t

INNER JOIN sys.partitions p

ON p.OBJECT_ID = t.OBJECT_ID

INNER JOIN sys.schemas s

ON t.schema_id = s.schema_id

WHERE t.is_ms_shipped = 0 AND p.index_id IN (0,1,2,3,4)

GROUP BY s.name,t.name

ORDER BY SUM(p.rows) DESC

Result:-

Table Name                            Rows

SalesOrderDetail                      121317

TransactionHistory                    113443

TransactionHistoryArchive        89253

WorkOrder                              72591

WorkOrderRouting                  67131

SalesOrderHeader                    31465

SalesOrderHeaderSalesReason            27647

Contact                                    19972