CODEWITHSUNDEEP
sql-server-2008-r2countrow
Pawan
Pawan

Reputation: 2218

How to get the table with the largest number of rows from database?

I am working on a SQL Server 2008R2 database. And I want to find out the table which has the most numbers of rows.

I have tried certain things, like below :

USE[SampleDatabase]

Select 
    tbl.name, si.rows 
from 
    sysindexes si
inner join 
    sys.tables tbl on si.id = tbl.object_id and indid < 2

But it's not the solution of my problem.

Upvotes: 0

Views: 176

Answers (1)

marc_s
marc_s

Reputation: 754488

Use this query:

SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts
FROM 
    sys.tables t
INNER JOIN  
    sys.partitions p ON t.object_id = p.object_id    
WHERE 
    t.is_ms_shipped = 0
GROUP BY 
    t.Name, p.Rows
ORDER BY 
    p.Rows DESC

Should give you all tables, and the number of rows they contain - ordered by the table with the most rows at the top.

Upvotes: 1

Related Questions