สำหรับสิ่งนี้ คุณสามารถใช้ INFORMATION_SCHEMA ต่อไปนี้เป็นไวยากรณ์ -
select my_schema.SCHEMA_NAME, group_concat(tbl.TABLE_NAME) from information_schema.SCHEMATA my_schemaleft join information_schema.TABLES tbl on my_schema.SCHEMA_NAME=tbl.TABLE_SCHEMAgroup by my_schema.SCHEMA_NAME;
ให้เราใช้ไวยากรณ์ข้างต้นเพื่อแสดงฐานข้อมูลทั้งหมดใน MySQL และสำหรับแต่ละฐานข้อมูล -
mysql> เลือก my_schema.SCHEMA_NAME, group_concat(tbl.TABLE_NAME) จาก information_schema.SCHEMATA my_schema ซ้าย เข้าร่วม information_schema.TABLES tbl บน my_schema.SCHEMA_NAME=tbl.TABLE_SCHEMAgroup โดย my_schema.SCHEMA_NAME;
สิ่งนี้จะสร้างผลลัพธ์ต่อไปนี้ -
<ก่อน>+---------------------------+------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------------------------ -------------------------------------------------- ----+| SCHEMA_NAME | group_concat(tbl.TABLE_NAME) |+--------------------------+-------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------------ -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------+| ทั้งอินโนดแบนด์ myisam | พนักงาน,grademo,นักเรียน,student_information || ธุรกิจ | addconstraintdemo, addonedaydemo, autoincrementtozero,booleandemo,college,columnvaluenulldemo,countcolumns,datatoolongdemo,deletedemo,demoemptyandnull,distinctdemo1,employeetable,findandreplacedemo,foreigntabledemo,groupdemoify1,indemodemondtime,indemo,indemo 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,mul tiplerowsinsert,newstudent,nulldemo1,orderdemo,พริม || commandline | caseinsensitivedistinctdemo,insertmaxplus1demo,อาจารย์,ค่าลบ tozerodemo || ติดตามลูกค้า | NULL || customer_tracker_database | NULL || ลูกค้าติดตาม | NULL || ฐานข้อมูล1 | NULL || ตัวอย่างฐานข้อมูล | NULL || สาธิต | mytable || การศึกษา | นักศึกษามหาวิทยาลัย || hb_student_tracker | demotable194,demotable202,demotable210,demotable218,demotable227,reorderintegerexcept0,demotable195,demotable203,demotable211,demotable219,demotable228,นักเรียน,demotable196,demotable204,demotable212,demotable220,demotable229,demotable,197,demotable demotable223,demotable231,demotable199,demotable207,demotable215,demotable224,demotable232,demotable192,demotable200,demotable208,demotable216,demotable225,demotable233,demotable193,demotable201,demotable209,demotable217,demotable2 | สวัสดี | NULL || information_schema | คอลัมน์ INNODB_CMP, INNODB_FIELDS, INNODB_FT_INDEX_TABLE, INNODB_TRX, PROCESSLIST, ST_SPATIAL_REFERENCE_SYSTEMS, VIEWS, เครื่องยนต์, INNODB_CMP_PER_INDEX, INNODB_FOREIGN, INNODB_INDEXES, INNODB_VIRTUAL โปรไฟล์, สถิติ, กิจกรรม, INNODB_CMP_PER_INDEX_RESET, INNODB_FOREIGN_COLS, INNODB_METRICS, KEY_COLUMN_USAGE, REFERENTIAL_CONSTRAINTS, TABLE_CONSTRAINTS, CHARACTER_SETS ไฟล์ INNODB_CMP_RESET, INNODB_FT_BEING_DELETED, INNODB_TABLES คำหลัก RESOURCE_GROUPS, TABLE_PRIVILEGES, COLLATION_CHARACTER_SET_APPLICABILITY, INNODB_BUFFER_PAGE, INNODB_CMPMEM, INNODB_FT_CONFIG, INNODB_TABLESPACES, OPTIMIZER_TRACE ประจำตาราง collations, INNODB_BUFFER_PAGE_LRU, INNODB_CMPMEM_RESET, INNODB_FT_DEFAULT_STOPWORD, INNODB_TABLESPACES_BRIEF พารามิเตอร์ 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_PR อิวิลเลจ || instant_app | NULL || javadatabase2 | NULL || javasampledatabase | NULL || เข้าสู่ระบบ | 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,selectrow_bug_demo,servoly func, innodb_index_stats, procs_priv, server_cost, tempvariableassignment, columns_priv, general_log, innodb_table_stats, proxies_priv เซิร์ฟเวอร์ TIME_ZONE ส่วนประกอบ 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 || ร้านหนังสือออนไลน์ | 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 บัญชี 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, กระทู้, 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 | เด็กผู้ชาย,เด็กผู้หญิง || ตัวอย่าง | 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,displayonly distinctvalue,employeeinformation,excludecertaincolumnsset limitwithstoredprocedure,mergingselectdemo,new_viewdemo,old_tabledemo2,orderbyfield,pipeinsertdemo,primarykeydemo,recievedatefromtable,removeuniquenessconstraint,returndemo,rowsusinglimit,secondtable,selectdistinctdemo,selectrecordsdemo,showsum inlastrowdemo,stoproundi || ฐานข้อมูลตัวอย่าง | NULL || schemaตัวอย่าง | NULL || เกมสล็อต | 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_summ ary_by_file_io,x$waits_b || ทดสอบ | absolutedifference, addcolumntocountasserialnumber, addingvaluetoexisting, allnamesstartwithaorborc, appenddatademo, autoincrementtabledemo, bigintdemo, boolorbooleanortinyintdemo, changecurrentautoincrementvalue, collatedemo, comparisononintfield, concatvalues, convertdatedemo, countingdemo, csvformatoutputs ลูกค้า 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,strin greplacedemo,ข้อมูลนักเรียน || test3 | groupwithtopndemo, productdemo, studentinformation, bestdateformatdemo, lastrecordbeforelastone, radiansdemo, updatestringdemo, bitdemo, mostrecentdatedemo, returnresultofmd5demo, countdemo, mysqlandconditiondemo, select_timestampdemo, deletedemo, nullcasedemo, selectt extafterlastslashdemo, differentdatetime คำสั่ง siglequotesdemo, expandedoutputdemo, orderbydatethentimedemo, singleautoincrementcolumndemo, fieldlessthan5chars โพสต์ storeddemo || ติดตาม | ตัวเลขป้องกัน || ฐานข้อมูลมหาวิทยาลัย | NULL || เว็บ | demotable492, demotable500, demotable508, demotable516, demotable525, demotable533,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,DemoTable,demotable605,demotable613,select,demotable2254917table,demobemobe2,demotable demotable267,demotable275,demotable283,demotable291,demotable299,demotable307,demotable315,demotable323,demotable331,demotable339,demotable347,demotable355,demotable363,demotable371,demotable379,demotable388,demotable404444,demotable demotable468, demotable476, demotable493, demotable501, demotable509, demotable517, demotable526, demotable534, DemoTable, DemoTable, DemoTable, DemoTable, DemoTable, DemoTable, DemoTable, DemoTable, demotable606,demotable,demotable614 demotable252, demotable260, demotable268, demotable276, || web_tracker | NULL || webtracker | NULL |+--------------------------+------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------+36 แถวในชุด 6 คำเตือน (0.18 วินาที)