4

So as the title says i need a query that finds the table that contains most 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 the indexes with this query:

select *
from sys.indexes

But how do i show the columns with most rows in the whole database?

Kind regards, Chris

Denys Séguret
  • 372,613
  • 87
  • 782
  • 758
Chris
  • 103
  • 1
  • 9
  • [How to fetch the row count for all tables in a SQL SERVER database](http://stackoverflow.com/questions/2221555/how-to-fetch-the-row-count-for-all-tables-in-a-sql-server-database) – huMpty duMpty Dec 18 '13 at 10:52

3 Answers3

6

I use this query usually 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 the firts just add TOP 1 after SELECT

--in reply 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
giammin
  • 18,620
  • 8
  • 71
  • 89
  • Thanks! This works, but how do i check if the answer i get is correct? – Chris Dec 18 '13 at 11:34
  • @Chris `select count(*) from TABLENAME` – giammin Dec 18 '13 at 11:35
  • And i dont get this part: WHERE t.name NOT LIKE 'dt%' AND i.object_id > 255 AND i.index_id <= 1 Can you explain? – Chris Dec 18 '13 at 11:37
  • I get the same results with this query: select top 1 name, max(rows) as AmountOfRows from sys.sysindexes group by name order by max(rows) desc – Chris Dec 18 '13 at 11:49
1

Using the answer to this question, you can run the following to see the table with the highest row count:

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
Community
  • 1
  • 1
greg84
  • 7,541
  • 3
  • 36
  • 45
  • I dont want to create a table, I alredy have a database containing tables, in which i want find which table has got the most rows, by using a query. Thanks for helping – Chris Dec 18 '13 at 10:58
  • It's not actually creating a table in the database. It's creating a temporary, in-memory table that gets removed afterwards. https://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/ You need it because the `sp_MSForEachTable` function returns a result set on each iteration, so without it you'd have to look through them manually to find the largest row count. – greg84 Dec 18 '13 at 11:00
  • The sp_MSForEachTable doesnt work "Msg 2812, Level 16, State 62, Line 7 Could not find stored procedure 'sp_MSForEachTable'." Does it help that i have a column in sys.indexes that is rowcnt? – Chris Dec 18 '13 at 11:18
  • 1
    Is your DB character set case sensitive? Try writing `sp_MSforeachtable` (only MS is upper case) – greg84 Dec 18 '13 at 11:26
  • You can use sys.indexes, I would guess it's a little faster as it doesn't have to scan the table, but the row count reflects uncommitted data and can be incorrect. – greg84 Dec 18 '13 at 11:30
0

You may also use this query to get no duplicates in table names:

Use[DataBase_Name]
select max(i.rows) as recordCounts,t.name as table_name, 
SCHEMA_NAME(t.schema_id) as schema_name from sysindexes i
inner join sys.tables t
on i.id=object_id
group by t.name, SCHEMA_NAME(t.schema_id)
Amr Omar
  • 1
  • 1