How can I display all databases in MySQL and for each database show all tables?

MySQLMySQLi Database

For this, you can use INFORMATION_SCHEMA. Following is the syntax −

select my_schema.SCHEMA_NAME, group_concat(tbl.TABLE_NAME)
from information_schema.SCHEMATA my_schema
left join information_schema.TABLES tbl on my_schema.SCHEMA_NAME=tbl.TABLE_SCHEMA
group by my_schema.SCHEMA_NAME;

Let us implement the above syntax in order to show all databases in MySQL and for each database −

mysql> select my_schema.SCHEMA_NAME, group_concat(tbl.TABLE_NAME)
   from information_schema.SCHEMATA my_schema
   left join information_schema.TABLES tbl on my_schema.SCHEMA_NAME=tbl.TABLE_SCHEMA
group by my_schema.SCHEMA_NAME;

This will produce the following output −

+---------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SCHEMA_NAME               | group_concat(tbl.TABLE_NAME)                                                                                            |
+---------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bothinnodbandmyisam       | employee,gradedemo,student,student_information                                                                          |
| business                  |
addconstraintdemo,addonedaydemo,autoincrementtozero,booleandemo,college,columnvaluenulldemo,countcolumns,datatoolongdemo,deletedemo,demoemptyandnull,distinctdemo1,employeetable,findandreplacedemo,foreigntabledemo,groupdemo1,indemo,intandbigint20demo,javadatetimedemo,likedemo,modifycolumndemo,multiplerecordwithvalues,nestedtransactiondemo,nulldemo,orderbydemo,primarytable1,randomintegerdemo,retrievefirst40characters,saveintotextfile,selectinwithbetweendemo,sortingdemo,studentrecordwithmyisam,tabledemo3,tblifdemo,textintonumberdemo,trailingandleadingdemo,tttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt,uniqueconstrainttable,updatewithlimit,variableastablename,adddatetimewithother,agecalculationfromdatetime,autostoredate,chardemo,colortable,commaseperatedemo,countrycitydemo,datatypedemo,deleterecord,demoindex,dropindex,enumdemo,finddemo,functionindexdemo,groupt_concatdemo,indexingdemo,intandtinyint,keydemo,limitoffsetdemo,modifycolumnnamedemo,multiplerowsinsert,newstudent,nulldemo1,orderdemo,prim                               |
| commandline                | caseinsensitivedistinctdemo,insertmaxplus1demo,instructor,negativevaluetozerodemo                                      |
| customer-tracker           | NULL                                                                                                                   |
| customer_tracker_database  | NULL                                                                                                                   |
| customertracker            | NULL                                                                                                                   |
| database1                  | NULL                                                                                                                   |
| databasesample             | NULL                                                                                                                   |
| demo                       | mytable                                                                                                                |
| education                  | student,university                                                                                                     |
| hb_student_tracker         | demotable194,demotable202,demotable210,demotable218,demotable227,reorderintegerexcept0,demotable195,demotable203,demotable211,demotable219,demotable228,student,demotable196,demotable204,demotable212,demotable220,demotable229,demotable197,demotable205,demotable213,demotable221,demotable230,demotable198,demotable206,demotable214,demotable223,demotable231,demotable199,demotable207,demotable215,demotable224,demotable232,demotable192,demotable200,demotable208,demotable216,demotable225,demotable233,demotable193,demotable201,demotable209,demotable217,demotable226,insertcurrentdate                                                                                                                 |
| hello                      | NULL                                                                                                                   |
| information_schema         | COLUMNS,INNODB_CMP,INNODB_FIELDS,INNODB_FT_INDEX_TABLE,INNODB_TRX,PROCESSLIST,ST_SPATIAL_REFERENCE_SYSTEMS,VIEWS,ENGINES,INNODB_CMP_PER_INDEX,INNODB_FOREIGN,INNODB_INDEXES,INNODB_VIRTUAL,PROFILING,STATISTICS,EVENTS,INNODB_CMP_PER_INDEX_RESET,INNODB_FOREIGN_COLS,INNODB_METRICS,KEY_COLUMN_USAGE,REFERENTIAL_CONSTRAINTS,TABLE_CONSTRAINTS,CHARACTER_SETS,FILES,INNODB_CMP_RESET,INNODB_FT_BEING_DELETED,INNODB_TABLES,KEYWORDS,RESOURCE_GROUPS,TABLE_PRIVILEGES,COLLATION_CHARACTER_SET_APPLICABILITY,INNODB_BUFFER_PAGE,INNODB_CMPMEM,INNODB_
FT_CONFIG,INNODB_TABLESPACES,OPTIMIZER_TRACE,ROUTINES,TABLES,COLLATIONS,INNODB_BUFFER_PAGE_LRU,INNODB_CMPMEM_RESET,INNODB_FT_DEFAULT_STOPWORD,INNODB_TABLESPACES_BRIEF,PARAMETERS,SCHEMA_PRIVILEGES,TABLESPACES,COLUMN_PRIVILEGES,INNODB_BUFFER_POOL_STATS,INNODB_COLUMNS,INNODB_FT_DELETED,INNODB_TABLESTATS,PARTITIONS,SCHEMATA,TRIGGERS,COLUMN_STATISTICS,INNODB_CACHED_INDEXES,INNODB_DATAFILES,INNODB_FT_INDEX_CACHE,INNODB_TEMP_TABLE_INFO,PLUGINS,ST_GEOMETRY_COLUMNS,USER_PRIVILEGES                                     |
| instant_app                | NULL                                                                                                                    |
| javadatabase2              | NULL                                                                                                                    |
| javasampledatabase         | NULL                                                                                                                    |
| login                      | NULL                                                                                                                    |
| mybusiness                 | NULL                                                                                                                    |
| mydatabase                 | NULL                                                                                                                    |
| mysql                      | db,help_category,mytable,selectdistinct_countdemo,slave_worker_info,time_zone_transition,default_roles,help_keyword,password_history,selectnonnumericvalue,slow_log,time_zone_transition_type,employee_information,help_relation,plugin,selectrowdemo,table,user,bugdemo,engine_cost,help_topic,prioritydemo,server,tables_priv,changevaluefrom1toy,func,innodb_index_stats,procs_priv,server_cost,tempvariableassignment,columns_priv,general_log,innodb_table_stats,proxies_priv,servers,time_zone,component,global_grants,limitdemo,removenumericcharaters,slave_master_info,time_zone_leap_second,countoccurrencesdemo,gtid_executed,md5demo,role_edges,slave_relay_log_info,time_zone_name                      |
| onetomanyrelationship      | NULL                                                                                                                   |
| onlinebookstore            | NULL                                                                                                                   |
| performance_schema         | events_errors_summary_by_user_by_error,events_stages_summary_by_user_by_event_name,events_statements_summary_by_digest,events_transactions_history_long,events_waits_history_long,file_summary_by_event_name,memory_summary_by_host_by_event_name,persisted_variables,replication_connection_configuration,session_variables,socket_summary_by_instance,table_lock_waits_summary_by_table,accounts,events_errors_summary_global_by_error,events_stages_summary_global_by_event_name,events_statements_summary_by_host_by_event_name,events_transactions_summary_by_account_by_event_name,events_waits_summary_by_account_by_event_name,file_summary_by_instance,memory_summary_by_thread_by_event_name,prepared_statements_instances,replication_connection_status,setup_actors,status_by_account,threads,cond_instances,events_stages_current,events_statements_current,events_statements_summary_by_program,events_transactions_summary_by_host_by_event_name,events_waits_summary_by_host_by_event_name,global_status,memory_summary_by_user_by_event_name,re                               |
| rdb                      | boy,girl                                                                                                                 |
| sample                   | accumulateddemo,addtotaltimedemo,autoincrementdemo,backtick_symboldemo,calculateaveragedemo,changecolumnname,columndoesnotexists,contains_capital_letterdemo,countbooleanfielddemo,crc32demo,decimal_demo,deletefrombasetabledemo,demotable10,demotable108,demotable115,demotable122,demotable13,demotable139,demotable146,demotable153,demotable160,demotable168,demotable175,demotable183,demotable190,demotable23,demotable240,demotable248,demotable255,demotable263,demotable28,demotable35,demotable43,demotable50,demotable58,demotable65,demotable72,demotable8,demotable87,demotable94,displayonlydistinctvalue,employeeinformation,excludecertaincolumnsdemo,findinsetdemo,firstweekofmonth,getfileextensiondemo,groupbydemo,idcolumnadd1demo,insert_prevent,javadatedemo,limitwithstoredprocedure,mergingselectdemo,new_viewdemo,old_tabledemo2,orderbyfield,pipeinsertdemo,primarykeydemo,recievedatefromtable,removeuniquenessconstraint,returndemo,rowsusinglimit,secondtable,selectdistinctdemo,selectrecordsdemo,showsuminlastrowdemo,stoproundi                               |
| sampledatabase          | NULL                                                                                                                      |
| schemasample            | NULL                                                                                                                      |
| slotgame                | NULL                                                                                                                      |
| sys                     | host_summary_by_file_io,io_by_thread_by_latency,memory_by_user_by_current_bytes,schema_object_overview,session_ssl_status,user_summary,wait_classes_global_by_latency,x$host_summary_by_statement_latency,x$io_global_by_wait_by_bytes,x$processlist,x$schema_table_statistics_with_buffer,x$statements_with_temp_tables,x$wait_classes_global_by_latency,host_summary_by_file_io_type,io_global_by_file_by_bytes,memory_global_by_current_bytes,schema_redundant_indexes,statement_analysis,user_summary_by_file_io,waits_by_host_by_latency,x$host_summary_by_statement_type,x$io_global_by_wait_by_latency,x$ps_digest_95th_percentile_by_avg_us,x$schema_tables_with_full_table_scans,x$user_summary,x$waits_by_host_by_latency,host_summary_by_stages,io_global_by_file_by_latency,memory_global_total,schema_table_lock_waits,statements_with_errors_or_warnings,user_summary_by_file_io_type,waits_by_user_by_latency,x$innodb_buffer_stats_by_schema,x$latest_file_io,x$ps_digest_avg_latency_distribution,x$session,x$user_summary_by_file_io,x$waits_b                               |
| test                   | absolutedifference,addcolumntocountasserialnumber,addingvaluetoexisting,allnamesstartwithaorborc,appenddatademo,autoincrementtabledemo,bigintdemo,boolorbooleanortinyintdemo,changecurrentautoincrementvalue,collatedemo,comparisononintfield,concatvalues,convertdatedemo,countingdemo,csvformatoutputs,customers,dateformatdemo,datetimedemo,decimaldemo,defaultdate,demoofvertical,differentrows,duplicaterecords,employeemaxandsecondmaxsalary,equivalentofidentityinmysql,findlowercasevalue,firsttablemaxvalue,getdatefromtimestamp,gettotalhoursdemo,ifnotnulldemo,incrementingdemo1,insertnullvalue,integerdemo,javainsertdemo,likebinarydemo,lowercasedemo,morethan2columnconcat,newstable,notnullatcreationoftable,occurrencesofcharacter,orderbycharacterlength,orderbynullfirstdemo,orlikedemo,populatetabledemo,productdemo,removenullrecordsdemo,renameviewdemo,rowendswithspecificstring,searchtextdemo,select_distincttwocolumns,selectindemo,selecttop2fromeachgroup,sessionvariabledemo,sortcolumnzeroatlastdemo,stringreplacedemo,studentinfo                               |
| test3                  | groupwithtopndemo,productdemo,studentinformation,bestdateformatdemo,lastrecordbeforelastone,radiansdemo,updatestringdemo,bitdemo,mostrecentdatedemo,returnresultofmd5demo,countdemo,mysqlandconditiondemo,select_timestampdemo,deletedemo,nullcasedemo,selectt extafterlastslashdemo,differentdatetime,order,siglequotesdemo,expandedoutputdemo,orderbydatethentimedemo,singleautoincrementcolumndemo,fieldlessthan5chars,posts,storeddemo |
| tracker                | preventnegativenumbers                                                                                                      |
| universitydatabase     | NULL                                                                                                                        |
| web                    | demotable492,demotable500,demotable508,demotable516,demotable525,demotable533,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,demotable605,demotable613,select,demotable484,demotabe619,demotable217,demotable225,demotable233,demotable241,demotable251,demotable259,demotable267,demotable275,demotable283,demotable291,demotable299,demotable307,demotable315,demotable323,demotable331,demotable339,demotable347,demotable355,demotable363,demotable371,demotable379,demotable388,demotable396,demotable404,demotable412,demotable420,demotable428,demotable436,demotable444,demotable452,demotable460,demotable468,demotable476,demotable493,demotable501,demotable509,demotable517,demotable526,demotable534,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,demotable606,demotable614,view_demotable388,demotable485,demotabe620,demotable218,demotable226,demotable234,demotable244,demotable252,demotable260,demotable268,demotable276,                                                                                 |
| web_tracker            | NULL                                                                                                                         |
| webtracker             | NULL                                                                                                                         |
+---------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
36 rows in set, 6 warnings (0.18 sec)
raja
Published on 23-Aug-2019 10:39:38
Advertisements