4.5.7 SHOW Syntax

   SHOW DATABASES [LIKE wild]
or SHOW [OPEN] TABLES [FROM db_name] [LIKE wild]
or SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]
or SHOW INDEX FROM tbl_name [FROM db_name]
or SHOW TABLE STATUS [FROM db_name] [LIKE wild]
or SHOW STATUS [LIKE wild]
or SHOW VARIABLES [LIKE wild]
or SHOW [BDB] LOGS
or SHOW [FULL] PROCESSLIST
or SHOW GRANTS FOR user
or SHOW CREATE TABLE table_name
or SHOW MASTER STATUS
or SHOW MASTER LOGS
or SHOW SLAVE STATUS
or SHOW WARNINGS [LIMIT row_count]
or SHOW ERRORS [LIMIT row_count]
or SHOW TABLE TYPES

SHOW provides information about databases, tables, columns, or status information about the server. If the LIKE wild part is used, the wild string can be a string that uses the SQL `%' and `_' wildcard characters.

Subsections

• 4.5.7.1 Retrieving information about Database, Tables, Columns, and Indexes
• 4.5.7.2 SHOW TABLE STATUS
• 4.5.7.3 SHOW STATUS
• 4.5.7.4 SHOW VARIABLES
• 4.5.7.5 SHOW [BDB] LOGS
• 4.5.7.6 SHOW PROCESSLIST
• 4.5.7.7 SHOW GRANTS
• 4.5.7.8 SHOW CREATE TABLE
• 4.5.7.9 SHOW WARNINGS | ERRORS
• 4.5.7.10 SHOW TABLE TYPES
• 4.5.7.11 SHOW PRIVILEGES

User Comments

how do you utilize the "SHOW DATABASES"
command in scripting (PHP)? For instance, if you
use a SELECT fieldname FROM tablename, you can
loop through the results and reference the fieldname
variable as row["fieldname"]. How do you reference
the results of the SHOW DATABASES? row
["databases"] does not work. The only usefullness
I've seen from this is with a command line php, I see
no way of utilizing the results of this query in PHP.

SHOW, EXPLAIN and other related commands
can be
manipulated in PHP just as if they were SELECTs,
e.g.:
$result = mysql_query("SHOW DATABASES");

while($row = mysql_fetch_assoc($result)) {
echo "<p>Database {$row['Database']}
exists.</p>\n";
}

You can use the USE <databasename> statement to define a default database for the SHOW statement (and for all mysql statements). Here's a typical sequence of statements to execute upon logging in in order to orient yourself:

SHOW DATABASES;
USE databaseOfInterest;
SHOW TABLES;
DESCRIBE tableOfInterest;

You also can easily copy tables with this command. Here I have the code for it:
____________________________________________________________

mysql_select_db("db",$link);
$query="show create table stap";
$result=mysql_query($query);
$row = mysql_fetch_array($result);
mysql_select_db("db_1",$link);
$query=$row[1];
mysql_query($query);
____________________________________________________________

Greetings

To show tables with PHP and MySQL:

mysql_select_db("foobar");
$query = "show tables";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);
print "There are $num_results tables.<br>";
for ($i = 0; $i < $num_results; $i++)
{
$row = mysql_fetch_array($result);
print "table " . $row[0] . " exists.<br>";
}

$row[0] will hold the table name.

Add your own comment.