การแสดงรายชื่อตารางเรียงลำดับตัวอักษรด้วย INFORMATION_SCHEMA.TABLES
คำสั่ง SHOW TABLES แสดงรายชื่อตาราง แต่ไม่รับประกันลำดับ (ขึ้นกับ Storage Engine) ใช้ INFORMATION_SCHEMA.TABLES กรองตาม Database แล้ว ORDER BY TABLE_NAME เพื่อเรียง A-Z
ไวยากรณ์ (Syntax)
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'yourDatabaseName'
ORDER BY TABLE_NAME;ตัวอย่าง: Database 'sample'
mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA = 'sample'
-> ORDER BY TABLE_NAME;ผลลัพธ์ (บางส่วน 342 ตาราง):
+------------------------------------+
| TABLE_NAME |
+------------------------------------+
| a |
| accumulateddemo |
| add10minutedemo |
| add1toexistingvalue |
| addanumbertocurrentvaluedemo |
| addcolumnandindexdemo |
| addingcurrentyeardemo |
| addprefixtocolumndemo |
| addtoexistingvaluedemo |
| addtotaltimedemo |
| addwhereclausedemo |
| aggregatefunctiondemo |
| allfiles |
| allownulldefaulnotnulldemo |
| allrecordsexceptlastone |
| ampmtimedisplaydemo |
| autoincrementdemo |
| autoincrementtabledemo |
| averagedemo |
| averagestring |
| avoidinserterrordemo |
| avoidinsertingduplicaterows |
| avoidnulldemo |
| b |
| backtick_symboldemo |
| bar |
| binarykeyworddemo |
| blobsizedemo |
| booleandemo |
| booleanevaluationdemo |
| bulkchangedemo |
| calculateaveragedemo |
| calculatepercentdemo |
| calculatevaluedemo |
| casefunctiondemo |
| caseinsensitivedemo |
| castfunctiondemo |
| casttypetobigintdemo |
| changecolumnname |
| charlengthdemo |
| child_table |
| childtable |
| client_information |
| clientdemo |
| clients |
| columndoesnotexists |
| combinetwocolumnsdemo |
| commadelimitedlist |
| comparedayandmonthdemo |
| comparetwostringdemo |
| comparingtimestampdemo |
| concatandlowerdemo |
| concatenatingdemo |
| contains_capital_letterdemo |
| convertnulltozerodemo |
| convertnumbertominute |
| correctdatetimedemo |
| correlationdemo |
| countallcharactersdemo |
| countallrowsdemo |
| countbooleanfielddemo |
| countdifferentdemo |
| countrowsdemo |
| counttop10demo |
| countvaluedemo |
| countwithsubquerydemo |
| coursedemo |
| crc32demo |
| createindexdemo |
| createviewdemo |
| currentweekdemo |
| datedemo |
| datedifferencedemo |
| dateformatdemo |
| dateortimedemo |
| decimal_demo |
| decimaldemo |
| defaulmyisam |
| defaultdemo |
| defaultenginedemo |
| delete_view |
| deleteafterspacedemo |
| deletedemo |
| deletefrombasetabledemo |
| deleterowdemo |
| deleterowsolderthan5demo |
| demo |
| department |
| department_table |
| detectbitdemo |
| displayonlydistinctvalue |
| distinctfromonecolumn |
| dividebycountdemo |
| donotselectduplicatevaluesdemo |
| duplicaterecords |
| employee |
| employee_table |
| employeeinformation |
| enforcedatausingregularexpression |
| enumdemo |
| equivalentdemo1 |
| equivalentdemo2 |
| errordemo |
| eurosymboldemo |
| eventdemo |
| excludecertaincolumnsdemo |
| existincommaseparatedlist |
| extractfilenamedemo |
| extracttuples |
| findandreplacedemo |
| findbestmatch |
| findcapitallettrsdemo |
| finddifferencedemo |
| findinsetdemo |
| findinvalidemailaddressdemo |
| findreplacedemo |
| findstringwithgivenprefixdemo |
| findvalue |
| first_table |
| firstandlastdatademo |
| firsttable |
| firstweekofmonth |
| followers |
| formatnumberdemo |
| formattime |
| formdemo |
| geom |
| get2daysagodemo |
| getdatetimefieldsdemo |
| getfileextensiondemo |
| getlatesthour |
| gettingrandomrow |
| getyearout |
| givenprecisiondemo |
| groupandcountbydate |
| groupbyandcountdemo |
| groupbydatedemo |
| groupbydemo |
| groupbygroupdemo |
| groupbymaxdemo |
| groupbyoneselectall |
| groupbyrangedemo |
| groupbywithwhereclause |
| hasconsecutiveprimary |
| idcolumnadd1demo |
| ifnulldemo |
| igonreyeardemo |
| inclausedemo |
| incrementcolumndemo |
| indemo |
| indexonintcolumndemo |
| inoperatordemo |
| insert_prevent |
| insertdemo |
| insertmultipledemo |
| insertonetoanothertable |
| insertrecord_selecttable |
| insertrecordprevent |
| insertvalueinautoincrement |
| instructor |
| javadatedemo |
| javagetdatademo |
| javapreparedstatement |
| keyreferringtodatedemo |
| lagdemo |
| largeautoincrement |
| lastinsertedrow |
| limit0demo |
| limitwithstoredprocedure |
| location |
| locktabledemo2 |
| loggingdetails |
| manage_table3 |
| maxdemo |
| maximumoftwocolumnsdemo |
| maxofthreecolumnsdemo |
| mergingselectdemo |
| modified_table2 |
| multiplecolumnsortingorderdemo |
| multiplegroupbydemo |
| my_table1 |
| mysqlselectdemo |
| mytable |
| new_viewdemo |
| newlinedemo |
| notequaloperator |
| notupdatedemo |
| null_demo |
| nulldemo |
| old_tabledemo |
| old_tabledemo2 |
| onlypositivevalue |
| orderbyafunctiondemo |
| orderbycasedemo |
| orderbydatedemo |
| orderbydateformatdemo |
| orderbyfield |
| orderbylast2charactersdemo |
| orderdemo |
| pairdemo |
| parent_table |
| parenttable |
| pipeconcatdemo |
| pipeinsertdemo |
| positivetonegativevaluedemo |
| positivevaluedemo |
| post |
| post_demo |
| postmesssagedemo |
| preventnegativenumberdemo |
| previousandnextrecorddemo |
| primarykeydemo |
| productinformations |
| products1 |
| products2 |
| productstock |
| quotesdemo |
| randomnumberdemo |
| receive |
| recievedatefromtable |
| recordsdemo |
| regularexpressiondemo |
| removedemo |
| removeerrordemo |
| removehyphensdemo |
| removeonupdatecurrenttimestampdemo |
| removespacedemo |
| removeuniquenessconstraint |
| reordersortdemo |
| replacevaluedemo |
| reservationsystems |
| reservingautoincrementdemo |
| resetprimarykeydemo |
| resettingprimarykeydemo |
| retrievetimedemo |
| returndemo |
| returnnullwheninputisnulldemo |
| returnrecordsetorderdemo |
| returnrows |
| reverseorder |
| roundweirddemo |
| rowafftectedbydeletedemo |
| rownumberdemo |
| rowsusinglimit |
| scorecountdemo |
| searchdateasvarchar |
| searchingdemo |
| searchingdemo2 |
| second_table |
| secondlastdemo |
| secondreceivedemo |
| secondtable |
| seespacesdemo |
| select1andlimit1demo |
| selectalldemo |
| selectallentriesdemo |
| selectbeforeandafterdemo |
| selectdaterangedemo |
| selectdatesdemo |
| selectdistinctdemo |
| selectifdemo |
| selectintoequivalentdemo |
| selectorderdemo |
| selectpartoftimestampdemo |
| selectpermonthdemo |
| selectperson |
| selectrandomrecord |
| selectrecordsdemo |
| selectuniquevalue |
| selfjoindemo |
| set+0demo |
| setallvaluesdemo |
| setautoincrementdemo |
| setfirstnameandlastname |
| setzerodemo |
| showsuminlastrowdemo |
| sleepdemo |
| sortcertainvalues |
| sortedlistdemo |
| specialcharactersdemo |
| sqlstatementsdemo |
| stoproundingdemo |
| stoproundingdemo2 |
| storecsscolordemo |
| storedproceduredemo |
| storedprocedureinsertdemo |
| storevalue0and1ornulldemo |
| stringendswithnumber |
| student_information |
| student_table_sample |
| subtotaldemo |
| subtractvaluedemo |
| sumdemo |
| sumofeverydistinct |
| sumtimedemo |
| syntaxofaltercommanddemo |
| taxonomytable |
| tbl1 |
| tblupdate |
| tematics_field |
| ternaryoperationdemo |
| texttotimestampdemo |
| timedemo |
| timestamp_tabledemo |
| top2demo |
| trimcommasdemo |
| uniquebothcolumnvaluesamedemo |
| uniquecountbyipadress |
| uniquedemo |
| university |
| unixtimestampformatdemo |
| unsigneddemo |
| updatecolumndemo |
| updatecolumnname |
| updatedatedemo |
| updatedemo |
| updateifconditiondemo |
| updatesettypedemo |
| updatewithhighestdemo |
| useofdesccolumnnamedemo |
| useprocedure |
| user_informations |
| usercommentsview |
| userdemo |
| userinformation |
| userinformationexpire |
| userlogindetails |
| userlogininformation |
| userlogintimeinformation |
| usertabledemo |
| utfdemo |
| valueascolumn |
| view_table_demo |
| viewautoincrementdemo |
| viewtabledemo |
| weeklyeventdemo |
| whereaftergroupdemo |
| wheredemo |
| wheresetdemo |
| wildcarddemo |
| yesterdayrecordsdemo |
| yourtable |
+------------------------------------+
342 rows in set (0.11 sec)คำอธิบาย
TABLE_SCHEMA= ชื่อ DatabaseTABLE_NAME= ชื่อตารางORDER BY TABLE_NAMEเรียง A-Z (ASCII: ตัวเลข → ตัวใหญ่ → ตัวเล็ก → สัญลักษณ์)
Query ขยาย: ดูข้อมูลเพิ่มเติม
SELECT TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS Size_MB
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'sample'
ORDER BY TABLE_NAME;แสดง: ชื่อ, ประเภท (BASE TABLE/VIEW), Engine, จำนวนแถวประมาณ, ขนาด (MB)
