SQL query that finds a table containing most rows in the database

Question

Since the name says I need a query that finds a table that contains most of the rows in my database.

I can show all my tables with this query:

select * from sys.tables

Or:

select *
from sysobjects
where xtype = 'U'
order by name

And all indexes with this query:

select *
from sys.indexes

But how do I show the columns with most rows in the entire database?

Regards, Chris

Answer 1

I usually use this query to sort all tables by rowcount:

USE DATABASENAME
SELECT t.NAME AS TableName, SUM(p.rows) AS RowCounts
FROM sys.tables t
    INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE t.NAME NOT LIKE 'dt%' AND i.OBJECT_ID > 255 AND i.index_id <= 1
GROUP BY t.NAME, i.object_id, i.index_id, i.name
ORDER BY SUM(p.rows) desc

If you want only firts added TOP 1afterSELECT

- in response to your comment ----

WHERE 
 t.NAME NOT LIKE 'dt%' AND --exclude Database Diagram tables like dtProperties
 i.OBJECT_ID > 255 AND  --exclude system-level tables
 i.index_id <= 1 -- avoid non clustered index

Answer 2

, , :

CREATE TABLE #counts
(
    table_name varchar(255),
    row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT TOP 1 table_name, row_count FROM #counts ORDER BY row_count DESC

DROP TABLE #counts