CODEWITHSUNDEEP
sqlmysqlsql-view
Glenn
Glenn

Reputation: 265

Query to count the exact number of records in all tables

IHello,

I'm trying to count for each table in my MySQL database how many records there are in it.

All of my tables happen to be in InnoDB and this query

SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'myschema';

is way much too much of an estimate (There are +846 records and it tells me there are only +-400)

Is there a way to count a more exact number of rows with a similar query? It doen't matter how long the query takes tot run. (P.S. not using php or a similar language)

Upvotes: 1

Views: 1307

Answers (3)

joaopintocruz
joaopintocruz

Reputation: 291

As posted in this Answer

create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable 
   'insert into #rowcount select ''?'', count(*) from ?'
select * from #rowcount
    order by tablename
drop table #rowcount

Upvotes: 0

JRGWV
JRGWV

Reputation: 164

If your are going to use the system views then you need to make sure your database statiscs are updated or the counts of rows will potentially be incorrect in the system schema views such as information_schema.x.

Use the 

select count(*) yourtable;

as stated in the answer above to get the exact number

Analyze table yourtable;

to update the statisitcs then check the count in the system view they should match at ths point for number of rows.

see this website for more details and a way to do this for your entire database:

MySQL reference manual

Upvotes: 0

DJ Quimby
DJ Quimby

Reputation: 3699

If the length of the query doesn't matter then what about doing the following for each table in your database:

select Count(*) from MyTable

Upvotes: 1

Related Questions