Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
MySQL - How to count all rows per table in one query?
You can count all rows per table with the help of aggregate function count (TABLE_ROWS) from informatio_schema.tables. The syntax is as follows −
SELECT table_name, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'yourDatabaseName';
Now you can apply the above syntax to get all rows per table. The query is as follows −
mysql> SELECT table_name, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'business';
Here is the output −
+------------------------------------------------------------------+------------+ | TABLE_NAME | TABLE_ROWS | +------------------------------------------------------------------+------------+ | accentsearchdemo | 2 | | add1daydemo | 2 | | addcheckconstraintdemo | 0 | | addcolumntable | 0 | | addconstraintdemo | 2 | | adding5hours | 2 | | addingautoincrement | 2 | | addingcolumnnamedemo | 0 | | addingunique | 2 | | addnotnulldemo | 2 | | agecalculationfromdatetime | 0 | | allcolumnsdemo | 0 | | alphademo | 0 | | altertabletoaddautoincrement | 2 | | autoincrement | 4 | | autoincrementtable | 5 | | autoincrementtozero | 2 | | backticksymbol | 4 | | bitdemo | 2 | | blobtabledemo | 0 | | bookindexes | 4 | | booleandemo | 0 | | chardemo | 0 | | checkconstraintdemo | 0 | | checkdemo | 0 | | checkingintegerdemo | 2 | | childdemo | 0 | | clonestudent | 3 | | college | 0 | | colortable | 0 | | columnexistdemo | 0 | | columnnameasnumberdemo | 2 | | columnnamewithspace | 4 | | columnslist | 0 | | columntabledemo | 0 | | columnvaluenulldemo | 2 | | commaseperatedemo | 2 | | commentdemo | 0 | | commentdemo2 | 0 | | commentdemo3 | 0 | | compositeprimarykey | 0 | | countrycitydemo | 2 | | curdatedemo | 0 | | currentdatetime | 0 | | currenttimeadding2hours | 0 | | currenttimezone | 1 | | dateadddemo | 0 | | dateandtimepartdemo | 0 | | datetimedemo | 2 | | defaultdatetimedemo | 0 | | deletecolumnnamedemo | 0 | | deletedemo | 5 | | deleterecord | 6 | | deletetabledemo | 2 | | demo | 2 | | demo1 | 0 | | demoascii | 2 | | demoauto | 2 | | demobcrypt | 0 | | demoemptyandnull | 0 | | demoindex | 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 | | firsttabledemo | 3 | | floordemo | 2 | | foreigntable | 2 | | foreigntable1 | 0 | | foreigntabledemo | 2 | | functionindexdemo | 0 | | functiontriggersdemo | 0 | | getlastrecord | 4 | | greatestoftwoormore | 2 | | groupbydemo | 4 | | groupconcatenatedemo | 4 | | groupdemo | 4 | | groupdemo1 | 4 | | groupt_concatdemo | 4 | | humandatedemo | 0 | | iddemo | 4 | | ifelsecasedemo | 4 | | ifelsedemo | 4 | | imagedemo | 2 | | incasesensdemo | 4 | | indemo | 6 | | indexingdemo | 0 | | insertdemo | 2 | | insertingemojidemo | 0 | | insertmultiplerowsdemo | 5 | | insertvaluesandsetdemo | 2 | | insubquerydemo | 2 | | int1demo | 0 | | intandbigint20demo | 0 | | intdemo | 2 | | inwithfielddemo | 12 | | ipv4addressdemo | 0 | | ipv6demo | 0 | | jasonasmysqldemo | 2 | | javadatetimedemo | 2 | | keydemo | 2 | | last10recordsdemo | 12 | | lastinsertiddemo | 2 | | lastinsertrecordiddemo | 2 | | latandlangdemo | 0 | | lengthandcharlengthdemo | 0 | | limitoffsetdemo | 11 | | limitoffsettable | 13 | | lowcardinality | 2 | | lowercasedemo | 0 | | mergedemo1 | 2 | | mergedemo2 | 0 | | milliseconddemo | 0 | | modifycolumndemo | 0 | | modifycolumnnamedemo | 0 | | modifydatatype | 0 | | moneydemo | 2 | | moneyrepresentation | 0 | | moneystoragedemo | 2 | | moviecollection | 6 | | multipleindexdemo | 0 | | multiplerecordwithvalues | 4 | | myisamtabledemo | 2 | | myisamtoinnodbdemo | 0 | | mysqltabledemo | 5 | | mytable | 0 | | mytable1 | 0 | | mytabledemo | 2 | | nestedtransactiondemo | 2 | | newstudent | 0 | | nextiddemo | 2 | | nextpreviousdemo | 9 | | nonasciidemo | 4 | | nowdemo | 0 | | nthrecorddemo | 4 | | nulldemo | 0 | | nulldemo1 | 4 | | nulldemowithzero | 6 | | nullwithselect | 6 | | numbercolumndemo | 0 | | numberofcolumns | 2 | | ondemo | 4 | | orderbydemo | 4 | | orderdemo | 2 | | originaltable | 4 | | parentdemo | 0 | | pasthistory | 4 | | presenthistory | 2 | | primarykeydemo | 0 | | primarytable | 2 | | primarytable1 | 2 | | primarytable1demo | 0 | | primarytabledemo | 2 | | proctabledemo | 3 | | product | 2 | | querybetweentwodates | 0 | | querydatedemo | 0 | | qutesdemo | 2 | | randomoptimizationdemo | 8 | | randoptimizedemo | 26 | | rankdemo | 5 | | repairtabledemo | 3 | | replacenullwithzerodemo | 2 | | rowcountdemo | 8 | | rowintocolumn | 4 | | rownumberdemo | 4 | | rows
Updated on: 30-Jul-2019149 Views
Advertisements
To Continue Learning Please Login