If you omit the table name, mysqldump displays the structure for alltables in the database.įor both mysqlshow and mysqldump, you can specify the usualconnection parameter options (such as -host or -user.) Determining Which Table Types Your Server Supports When using mysqldump to review table structure, be sure toinvoke it with the -no-data option so that you don't get swampedwith your table's data! % mysqldump -no-data db_name tbl_name The mysqldump utility allows you to see the structure of your tablesin the form of a CREATE TABLE statement (much like SHOW CREATETABLE). List tables in the named database: % mysqlshow db_nameĭisplay information about columns in the named table: % mysqlshow db_name tbl_nameĭisplay information about indexes in the named table: % mysqlshow -keys db_name tbl_nameĭisplay descriptive information about tables in the named database: % mysqlshow -status db_name List databases managed by the server: % mysqlshow The mysqlshow command provides some of the same information as the SHOW statement, which allows you to get database and table informationfrom the shell: Generally, this is done to match a literal' _', which occurs frequently in database, table, and columnnames. To match a literal instance of a wildcard character in a LIKEpattern, precede it with a backslash. Forexample, the following statement displays the names of tables in the currentdatabase that begin with 'geo': SHOW TABLES LIKE 'geo%' ' pat' is interpreted as a SQL pattern that can includethe ' %' and ' _' wildcard characters. Several forms of SHOW take a LIKE ' pat'clause allowing a pattern to be given that limits the scope of the output. This statement was introduced in MySQL 3.23.20. This statement was introduced in MySQL 3.23.0.ĭisplay the CREATE TABLE statement that corresponds to the current structure of a table: SHOW CREATE TABLE tbl_name The DESCRIBE tbl_name and EXPLAIN tbl_name statements are synonymous with SHOW COLUMNS FROM tbl_name.ĭisplay descriptive information about tables in the current database or in a given database: SHOW TABLE STATUS SHOW TABLE STATUS FROM db_name Note that SHOW TABLES doesn't show TEMPORARY tables.ĭisplay information about columns or indexes in a table: SHOW COLUMNS FROM tbl_name SHOW INDEX FROM tbl_name List the tables in the current database or in a given database: SHOW TABLES SHOW TABLES FROM db_name

List the databases managed by the server: SHOW DATABASES The SHOW statement can be used to obtain information about severalaspects of your databases and tables: You can also use SHOWprior to issuing ALTER TABLE it's often easier to figure out howto specify a change to a column after you determine the column's currentdefinition. SHOW ishelpful for keeping track of the contents of your databases and for remindingyourself about the structure of your tables. MySQL provides a SHOW statement that has several variant forms thatdisplay information about databases and the tables in them. Learn More Buy Getting Information about Databases and Tables SQL in 24 Hours, Sams Teach Yourself, 7th Edition

Mysql list databases query