- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
Get record count for all tables in MySQL database?
To get the count of all the records in MySQL tables, we can use TABLE_ROWS with aggregate function SUM. The syntax is as follows.
SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'yourDatabaseName';
Apply the above syntax in order to get the count of records for all tables. The query is as follows −
mysql> SELECT SUM(TABLE_ROWS) ->FROM INFORMATION_SCHEMA.TABLES ->WHERE TABLE_SCHEMA = 'business';
The following table returns the count of records.
+-----------------+ | SUM(TABLE_ROWS) | +-----------------+ | 450 | +-----------------+ 1 row in set (13.54 sec)
To display the count of all records per table, use the following query −
mysql> SELECT table_name, table_rows ->FROM INFORMATION_SCHEMA.TABLES ->WHERE TABLE_SCHEMA = 'business';
The following is the output.
+------------------------------------------------------------------+------------+ | TABLE_NAME | TABLE_ROWS | +------------------------------------------------------------------+------------+ | add1daydemo | 2 | | addcheckconstraintdemo | 0 | | addcolumntable | 0 | | addconstraintdemo | 2 | | adding5hours | 2 | | addingunique | 2 | | addnotnulldemo | 2 | | alphademo | 0 | | autoincrement | 4 | | autoincrementtable | 5 | | backticksymbol | 4 | | bitdemo | 2 | | blobtabledemo | 0 | | bookindexes | 4 | | booleandemo | 0 | | chardemo | 0 | | checkdemo | 0 | | checkingintegerdemo | 2 | | childdemo | 0 | | clonestudent | 3 | | college | 0 | | colortable | 0 | | columnexistdemo | 0 | | columnnameasnumberdemo | 2 | | columnnamewithspace | 4 | | columnslist | 0 | | columnvaluenulldemo | 2 | | commaseperatedemo | 2 | | commentdemo | 0 | | commentdemo2 | 0 | | commentdemo3 | 0 | | countrycitydemo | 2 | | currentdatetime | 0 | | currenttimeadding2hours | 0 | | currenttimezone | 1 | | dateadddemo | 0 | | datetimedemo | 3 | | deletedemo | 5 | | deleterecord | 6 | | demo | 2 | | demo1 | 0 | | demoascii | 2 | | demoauto | 2 | | demobcrypt | 0 | | demoemptyandnull | 0 | | demoint | 0 | | demoonreplace | 2 | | demoschema | 0 | | demowhere | 2 | | distcountdemo | 4 | | distinctdemo | 8 | | distinctdemo1 | 4 | | duplicatebookindexes | 4 | | duplicatedeletedemo | 4 | | duplicatefound | 4 | | employeeinformation | 2 | | employeerecords | 0 | | employeetable | 0 | | enumdemo | 2 | | enumvalues | 0 | | escapedeom | 0 | | existsrowdemo | 4 | | findandreplacedemo | 4 | | finddemo | 2 | | firsttable | 2 | | firsttabledemo | 3 | | foreigntable | 2 | | foreigntabledemo | 2 | | functionindexdemo | 0 | | functiontriggersdemo | 0 | | groupconcatenatedemo | 4 | | groupdemo | 4 | | groupdemo1 | 4 | | groupt_concatdemo | 4 | | ifelsedemo | 4 | | imagedemo | 2 | | incasesensdemo | 4 | | indexingdemo | 0 | | insertingemojidemo | 1 | | insubquerydemo | 2 | | int1demo | 0 | | intdemo | 2 | | ipv4addressdemo | 0 | | ipv6demo | 0 | | jasonasmysqldemo | 2 | | keydemo | 2 | | last10recordsdemo | 12 | | lastinsertiddemo | 3 | | lastinsertrecordiddemo | 3 | | latandlangdemo | 0 | | lengthandcharlengthdemo | 1 | | limitoffsetdemo | 11 | | lowcardinality | 2 | | milliseconddemo | 0 | | modifycolumnnamedemo | 0 | | modifydatatype | 0 | | moneydemo | 2 | | moviecollection | 6 | | multipleindexdemo | 0 | | multiplerecordwithvalues | 4 | | myisamtabledemo | 2 | | myisamtoinnodbdemo | 0 | | mytable | 0 | | mytable1 | 0 | | mytabledemo | 2 | | newstudent | 0 | | nextiddemo | 2 | | nextpreviousdemo | 9 | | nonasciidemo | 4 | | nthrecorddemo | 4 | | nulldemo | 0 | | nullwithselect | 6 | | numbercolumndemo | 0 | | numberofcolumns | 2 | | ondemo | 4 | | orderdemo | 2 | | originaltable | 4 | | parentdemo | 0 | | pasthistory | 4 | | presenthistory | 2 | | primarytable | 2 | | primarytable1 | 2 | | primarytabledemo | 2 | | proctabledemo | 3 | | querybetweentwodates | 0 | | querydatedemo | 0 | | qutesdemo | 2 | | randomoptimizationdemo | 8 | | randoptimizedemo | 26 | | repairtabledemo | 3 | | rowcountdemo | 8 | | rowintocolumn | 4 | | rownumberdemo | 4 | | rowstranspose | 2 | | rowstransposedemo | 4 | | rowvaluedemo | 8 | | saveintotextfile | 2 | | saveoutputintext | 0 | | schemadatabasemethoddemo | 0 | | secondtable | 2 | | secondtabledemo | 2 | | sequencedemo | 7 | | singlequotesdemo | 2 | | smallintdemo | 0 | | sortingvarchardemo | 6 | | sourcetable | 4 | | spacecolumn | 2 | | stringoccurrencedemo | 3 | | stringtodatedemo | 0 | | student | 2 | | studentenrollment | 0 | | studentrecordwithmyisam | 0 | | studenttable | 4 | | swappingtwocoulmnsvaluedemo | 5 | | table1 | 2 | | table2 | 3 | | tabledemo | 0 | | tabledemo2 | 0 | | tabledemo3 | 0 | | tableforeign | 0 | | tablename1tablename1tablename1tablename1tablename1tablename1demo | 0 | | tablepri | 0 | | tbldemotrail | 6 | | tblf | 0 | | tblfirst | 2 | | tblfunctiontrigger | 0 | | tblifdemo | 4 | | tblnull | 0 | | tblp | 0 | | tblselectdemo | 6 | | tblstudent | 2 | | tbluni | 0 | | tblupdatelimit | 8 | | textdemo | 0 | | textintonumberdemo | 4 | | texttabledemo | 0 | | texturl | 0 | | timestampdemo | 0 | | timestamptodatedemo | 0 | | tinyint1demo | 0 | | tinyintdemo | 2 | | trailingandleadingdemo | 2 | | transcationdemo | 2 | | triggedemo | 0 | | trigger1 | 0 | | trigger2demo | 0 | | trimdemo | 2 | | trimdemo2 | 0 | | truefalsetable | 0 | | tttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt | 0 | | uniondemo1 | 3 | | uniondemo2 | 7 | | uniqueautoid | 3 | | uniqueconstdemo | 2 | | uniquedemo | 2 | | uniquedemo1 | 2 | | unsigneddemo | 2 | | updatewithlimit | 5 | | updtable | 4 | | usernameandpassworddemo | 2 | | varchardemo | 0 | | varchardemo1 | 0 | | varchardemo2 | 0 | | varcharurl | 0 | | variableastablename | 2 | | variablenametable | 0 | | whereconditon | 4 | | wordcountdemo | 0 | | xmldemo | 0 | +------------------------------------------------------------------+------------+ 209 rows in set (0.08 sec)
- Related Articles
- How can I display all databases in MySQL and for each database show all tables?
- List down all the Tables in a MySQL Database
- How to count the number of tables in a MySQL database?
- Display all tables inside a MySQL database using Java?
- Exact count of all rows in MySQL database?
- Get all the tables from a MySQL database having a specific column, let’s say xyz?
- How to get the list of tables in default MySQL database?
- Get the last record from a table in MySQL database with Java?
- Get a list of non-empty tables in a particular MySQL database?
- How to get the size of the tables of a MySQL database?
- MySQL count(*) from multiple tables?
- How can I describe all tables in the database through a single statement in MySQL?
- Count(*) rows from multiple tables in MySQL?
- Total number of fields in all tables in database?\n
- How to show all the tables present in the database and server in MySQL using Python?
Advertisements