Count number of rows in each table in MySQL?

To get the count of rows, you need to use information_schema.tables. The syntax is as follows.

SELECT table_name, table_rows
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA = ‘yourDatabaseName’;

Let us implement the above syntax for a database with the name ‘test’. The query is as follows displaying the table names with the count of rows in the table.

mysql> SELECT table_name, table_rows
-> FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA = 'test';

The following is the output.

+------------------------------------+------------+
| TABLE_NAME | TABLE_ROWS |
+------------------------------------+------------+
| _student_trackerdemo | 0 |
| _studenttrackerdemo | 0 |
| add30minutesdemo | 0 |
| addcolumn | 0 |
| addingcurrencysymboldemo | 4 |
| addingdaysdemo | 8 |
| addoneday | 2 |
| agecalculatesdemo | 4 |
| agedemo | 4 |
| aliasdemo | 2 |
| allcharacterbeforespace | 4 |
| allownulldemo | 2 |
| allrecordsfromadate | 5 |
| allrecordswithactive | 6 |
| allrows | 4 |
| appenddatademo | 3 |
| appendingdatademo | 2 |
| autoincrementdemo | 6 |
| autoincrementdemo1 | 0 |
| autoincrementedprimary | 4 |
| autoincrementspecificpoint | 6 |
| autoincrementwithmaxvaluefromtable | 2 |
| autostoredatetime | 0 |
| averageontime | 4 |
| base64demo | 2 |
| betweendatedemo | 6 |
| bigintandintdemo | 0 |
| bigintdemo | 0 |
| bigintdemo8 | 6 |
| bitdemo | 2 |
| bookdatedemo | 2 |
| booldemo | 0 |
| booleandemo | 0 |
| casedemo | 2 |
| caseupdatedemo | 4 |
| changecellsdata | 4 |
| changecolumnposition | 0 |
| changecolumnpositiondemo | 4 |
| changecurrentautoincrementvalue | 6 |
| changeenginetabledemo | 0 |
| charsetdemo | 0 |
| columnwithspace | 4 |
| comparingdate | 4 |
| concatenatetwocolumnsdemo | 4 |
| concatenation_operatordemo | 4 |
| concatenationdemo | 4 |
| conditionalinsertdemo | 4 |
| constraintdemo | 0 |
| convertdatetimetodate | 4 |
| countingdemo | 4 |
| creatingtable | 7 |
| creatingtableusingviewstudent | 0 |
| cumulativesumdemo | 4 |
| currentdatetimedemo | 0 |
| customers | 0 |
| databystringlength | 4 |
| dateandtimetotimestamp | 4 |
| dateasstringdemo | 2 |
| datecreatedemo | 4 |
| dateequaltoday | 6 |
| dateformatdemo | 4 |
| dateformatwithselect | 6 |
| datefromtimestamp | 4 |
| dateinsertdemo | 2 |
| daterange | 8 |
| datesofoneweek | 4 |
| datetimedemo | 6 |
| dayofweekdemo | 6 |
| daysinagivenmonth | 6 |
| decimaldemo | 4 |
| decimaltointdemo | 2 |
| decrementdemo | 6 |
| defaultdemo | 0 |
| deleteallfromtable | 0 |
| deletedemo | 5 |
| deletemanyrows | 2 |
| demo | 2 |
| demo1 | 2 |
| destination | 0 |
| differenceinhours | 2 |
| differenceinseconds | 4 |
| differencetimestamp | 2 |
| differentrows | 4 |
| display | 2 |
| displayint | 5 |
| distinctdemo | 7 |
| empidandempname_view | 0 |
| empinfoview | 0 |
| employee | 2 |
| employeedesignation | 2 |
| employeeinformation | 6 |
| employeetable | 2 |
| emptycellsatend | 9 |
| emptyin | 4 |
| enumdemo | 0 |
| fetchrowlasthourdemo | 6 |
| findlowercasevalue | 4 |
| firsttabledemo | 2 |
| firsttablemaxvalue | 6 |
| firstworddemo | 2 |
| formatnumbertwodecimalplace | 6 |
| generatingnumbersdemo | 10 |
| getdatefromtimestamp | 4 |
| getfirstdayofmonth | 4 |
| gettinglast5characters | 4 |
| gmailsignin | 4 |
| groupbytwofieldsdemo | 6 |
| groupmonthandyeardemo | 4 |
| highestidorderby | 6 |
| highestnumberdemo | 4 |
| ifnulldemo | 4 |
| increaseanddecreasedemo | 2 |
| increasevarchardemo | 0 |
| incrementanddecrementvalue | 2 |
| incrementby1 | 6 |
| incrementcounterdemo | 6 |
| inparameter | 5 |
| insert | 0 |
| insertignoredemo | 2 |
| insertingnull | 0 |
| insertnulldemo | 3 |
| insertwithmultipleandsigle | 21 |
| int11demo | 2 |
| intcurrencydemo | 5 |
| intrangedemo | 2 |
| inttodatedemo | 4 |
| intvsintanythingdemo | 4 |
| isnulldemo | 6 |
| jsonformatdemo | 4 |
| keywordsearchdemo | 6 |
| lasthourrecords | 4 |
| lasttwocharacters | 2 |
| likebinarydemo | 0 |
| likedemo | 4 |
| limitafterorderby | 4 |
| lookuptable | 0 |
| lowercasedemo | 2 |
| maxlengthfunctiondemo | 4 |
| monthdemo | 12 |
| moviecollectiondemo | 0 |
| multiplecountdemo | 8 |
| myisamtoinnodbdemo | 0 |
| nearestdatedemo | 4 |
| newstable | 6 |
| newtableduplicate | 3 |
| notempty | 0 |
| notequaldemo | 7 |
| notequalsdemo | 5 |
| notinsertemptystring | 0 |
| nowandcurdatedemo | 2 |
| nowdemo | 2 |
| nthrecorddemo | 6 |
| nullandemptydemo | 7 |
| nullatbottom | 6 |
| onlyhourdemo | 3 |
| onlymonthandyear | 4 |
| orderbycharacterlength | 4 |
| orderbyif | 6 |
| orderbynullfirstdemo | 4 |
| orderbyrandname | 8 |
| orderbyrelevance | 4 |
| orderindemo | 6 |
| originaltable | 4 |
| parsedatedemo | 4 |
| passinganarraydemo | 8 |
| persons | 0 |
| prependstringoncolumnname | 2 |
| pricedemo | 4 |
| primarykey1000demo | 4 |
| proceduredemotable | 2 |
| queryresultdemo | 4 |
| renameviewdemo | 0 |
| reordercolumn | 0 |
| replacedemo | 4 |
| rowexistdemo | 4 |
| rowpositiondemo | 4 |
| rowwithsamevalue | 4 |
| safedeletedemo | 4 |
| searchdemo | 6 |
| searchtextdemo | 0 |
| secondtabledemo | 2 |
| secondtablerows | 4 |
| selectconcat | 5 |
| selectdataonyearandmonthdemo | 4 |
| selectdistincttwocolumns | 8 |
| selectdomainnameonly | 4 |
| sha256demo | 0 |
| simulatearraydemo | 6 |
| skiplasttenrecords | 0 |
| smallintdemo | 5 |
| sortbydateandtime | 4 |
| sortcolumnzeroatlastdemo | 6 |
| sortingstringdemo | 5 |
| startautoincrement | 3 |
| storedproctable | 2 |
| stringreplacedemo | 2 |
| stringtodate | 2 |
| stringwithspecificlength | 4 |
| student | 2 |
| studentdemo | 4 |
| studentinformation | 5 |
| studentmodifytabledemo | 3 |
| studenttable | 3 |
| stuedntinformation | 0 |
| substringindexdemo | 4 |
| subtract3hours | 2 |
| sumcasedemo | 6 |
| sumdemooncolumns | 4 |
| sumwithifcondition | 6 |
| tablealiasdemo | 4 |
| tableview | 0 |
| tblemployee | 0 |
| temporarycolumnwithvaluedemo | 4 |
| timetosecond | 2 |
| timetoseconddemo | 0 |
| tinyintdemo | 0 |
| toggledemo | 6 |
| toogledemo | 6 |
| triggerdemo1 | 3 |
| triggerdemo2 | 0 |
| truncatetabledemo | 0 |
| union_table1 | 3 |
| union_table2 | 3 |
| unixtime | 2 |
| unixtimedemo | 0 |
| updatealldemo | 2 |
| updatedate | 4 |
| updatenumber1to3 | 3 |
| updaterowwith1to3 | 6 |
| updatevalueincrementally | 5 |
| updatewithifcondition | 2 |
| uppertabledemo | 4 |
| userdateformat | 4 |
| userrole | 4 |
| uservariable | 2 |
| uservariableinlike | 5 |
| view_student | 0 |
| viewdemo | 2 |
| viewemployeeidandemployeename | 0 |
| wheredemo | 4 |
| wholewordmatchdemo | 2 |
| yesterdaydatedemo | 2 |
| zipcodepadwithzerodemo | 4 |
+------------------------------------+------------+
243 rows in set (0.86 sec)

Chandu yadav

Updated on: 30-Jul-2019

3K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started