Get record count for all tables in MySQL database?

MySQLMySQLi 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)
raja
Published on 29-Nov-2018 12:42:43
Advertisements