:: DEVELOPER ZONE

MySQL 5.0 Reference Manual :: 13 SQL Statement Syntax :: 13.5 Database Administration Statements :: 13.5.4 SHOW Syntax

  • Overview
  • MySQL Reference Manual
  • 3.23, 4.0, 4.1
  • 5.0
  • 5.1
  • MaxDB Documentation
  • MySQL 5.0 Reference Manual
  • 13.5 Database Administration Statements
  • 13.5.1 Account Management Statements
  • 13.5.2 Table Maintenance Statements
  • 13.5.3 SET Syntax
  • 13.5.4 SHOW Syntax
    • 13.5.4.1 SHOW CHARACTER SET Syntax
    • 13.5.4.2 SHOW COLLATION Syntax
    • 13.5.4.3 SHOW COLUMNS Syntax
    • 13.5.4.4 SHOW CREATE DATABASE Syntax
    • 13.5.4.5 SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION Syntax
    • 13.5.4.6 SHOW CREATE TABLE Syntax
    • 13.5.4.7 SHOW CREATE VIEW Syntax
    • 13.5.4.8 SHOW DATABASES Syntax
    • 13.5.4.9 SHOW ENGINE Syntax
    • 13.5.4.10 SHOW ENGINES Syntax
    • 13.5.4.11 SHOW ERRORS Syntax
    • 13.5.4.12 SHOW GRANTS Syntax
    • 13.5.4.13 SHOW INDEX Syntax
    • 13.5.4.14 SHOW INNODB STATUS Syntax
    • 13.5.4.15 SHOW LOGS Syntax
    • 13.5.4.16 SHOW OPEN TABLES Syntax
    • 13.5.4.17 SHOW PRIVILEGES Syntax
    • 13.5.4.18 SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS Syntax
    • 13.5.4.19 SHOW PROCESSLIST Syntax
    • 13.5.4.20 SHOW STATUS Syntax
    • 13.5.4.21 SHOW TABLE STATUS Syntax
    • 13.5.4.22 SHOW TABLES Syntax
    • 13.5.4.23 SHOW TRIGGERS Syntax
    • 13.5.4.24 SHOW VARIABLES Syntax
    • 13.5.4.25 SHOW WARNINGS Syntax
  • 13.5.5 Other Administrative Statements

13.5.4. SHOW Syntax

13.5.4.1. SHOW CHARACTER SET Syntax
13.5.4.2. SHOW COLLATION Syntax
13.5.4.3. SHOW COLUMNS Syntax
13.5.4.4. SHOW CREATE DATABASE Syntax
13.5.4.5. SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION Syntax
13.5.4.6. SHOW CREATE TABLE Syntax
13.5.4.7. SHOW CREATE VIEW Syntax
13.5.4.8. SHOW DATABASES Syntax
13.5.4.9. SHOW ENGINE Syntax
13.5.4.10. SHOW ENGINES Syntax
13.5.4.11. SHOW ERRORS Syntax
13.5.4.12. SHOW GRANTS Syntax
13.5.4.13. SHOW INDEX Syntax
13.5.4.14. SHOW INNODB STATUS Syntax
13.5.4.15. SHOW LOGS Syntax
13.5.4.16. SHOW OPEN TABLES Syntax
13.5.4.17. SHOW PRIVILEGES Syntax
13.5.4.18. SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS Syntax
13.5.4.19. SHOW PROCESSLIST Syntax
13.5.4.20. SHOW STATUS Syntax
13.5.4.21. SHOW TABLE STATUS Syntax
13.5.4.22. SHOW TABLES Syntax
13.5.4.23. SHOW TRIGGERS Syntax
13.5.4.24. SHOW VARIABLES Syntax
13.5.4.25. SHOW WARNINGS Syntax

SHOW has many forms that provide information about databases, tables, columns, or status information about the server. This section describes those following: 

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']
SHOW CREATE DATABASE db_name
SHOW CREATE FUNCTION funcname
SHOW CREATE PROCEDURE procname
SHOW CREATE TABLE tbl_name
SHOW DATABASES [LIKE 'pattern']
SHOW ENGINE engine_name {LOGS | STATUS }
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW FUNCTION STATUS [LIKE 'pattern']
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW INNODB STATUS
SHOW PROCEDURE STATUS [LIKE 'pattern']
SHOW [BDB] LOGS
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']
SHOW TRIGGERS
SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']
SHOW WARNINGS [LIMIT [offset,] row_count]

The SHOW statement also has forms that provide information about replication master and slave servers and are described in Section 13.6, “Replication Statements”: 

SHOW BINLOG EVENTS
SHOW MASTER LOGS
SHOW MASTER STATUS
SHOW SLAVE HOSTS
SHOW SLAVE STATUS

If the syntax for a given SHOW statement includes a LIKE 'pattern' part, 'pattern' is a string that can contain the SQL ‘%’ and ‘_’ wildcard characters. The pattern is useful for restricting statement output to matching values.

Several SHOW statements also accept a WHERE clause that provides more flexibility in specifying which rows to display. See Section 20.18, “Extensions to SHOW Statements”.


User Comments

Posted by John Hicks on February 28 2003 9:07am[Delete] [Edit]

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;

Posted by Paul Kok on March 5 2003 5:34am[Delete] [Edit]

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

Posted by Kit Peters on May 15 2003 9:40am[Delete] [Edit]

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.

Posted by Roberto Spadim on October 17 2003 2:53am[Delete] [Edit]

you can use this command too...
<?
$db=mysql_connect($host,$user,$passwd);
$tbl=mysql_query("SHOW DATABASES");
for ($i=0;$i<mysql_num_rows($tbl);$i++){
  $database=mysql_result($tbl,$i,0);
}
.
.
.
?>

Add your own comment.




real_vine@hotmail.com