4.5.7.1 Retrieving information about Database, Tables, Columns, and Indexes

You can use db_name.tbl_name as an alternative to the tbl_name FROM db_name syntax. These two statements are equivalent:

mysql> SHOW INDEX FROM mytable FROM mydb;
mysql> SHOW INDEX FROM mydb.mytable;

SHOW DATABASES lists the databases on the MySQL server host. You can also get this list using the mysqlshow command line tool. In version 4.0.2 you will only see those databases for which you have some kind of privilege, if you don't have the global SHOW DATABASES privilege.

SHOW TABLES lists the tables in a given database. You can also get this list using the mysqlshow db_name command.

Note: if a user doesn't have any privileges for a table, the table will not show up in the output from SHOW TABLES or mysqlshow db_name.

SHOW OPEN TABLES lists the tables that are currently open in the table cache. See section 5.4.7 How MySQL Opens and Closes Tables. The Comment field tells how many times the table is cached and in_use.

SHOW COLUMNS lists the columns in a given table. If you specify the FULL option, you will also get the privileges you have for each column. If the column types are different from what you expect them to be based on a CREATE TABLE statement, note that MySQL sometimes changes column types. See section 6.5.3.1 Silent Column Specification Changes. As of MySQL 4.1, the FULL keyword also causes any per-column comments to be displayed.

The DESCRIBE statement provides information similar to SHOW COLUMNS. See section 6.6.2 DESCRIBE Syntax (Get Information About Columns).

SHOW FIELDS is a synonym for SHOW COLUMNS, and SHOW KEYS is a synonym for SHOW INDEX. You can also list a table's columns or indexes with mysqlshow db_name tbl_name or mysqlshow -k db_name tbl_name.

SHOW INDEX returns the index information in a format that closely resembles the SQLStatistics call in ODBC. The following columns are returned:

Note that as the Cardinality is counted based on statistics stored as integers, it's not necessarily accurate for small tables.

The Null and Index_type columns were added in MySQL 4.0.2.

User Comments

Note, there does not appear to be a way to read
the actual contents of an index, unfortunately.

How retrieving information about table constraints, checks?

Dont try optimize table on a table that has a
full text index, unless you can afford your
database to be down for half the day... We tried
it, it bit us.

Since MySQL does not provide constraints nor
checks, you can't SHOW them.

It isn't obvious from the descriptions above, but
the SHOW COLUMNS statement requires a FROM
tablename clause.

For ALEX...

OPTIMIZE TABLE table_name

...does the trick on my end

It used to be that MySQL save EVERYTHING that was
in the CREATE TABLE statement, even if it didn't
use it or pay attention to it. So the question
about contraints is valid. The purpose, I would
imagine, is to be able to export the table
creation statements at a later time to another
database, or when MySQL develops the features that
would use the contraints, et. al.

The lack of information on foreign keys is disturbing
as it is most helpful to understand a schema.

Another issue are comments to a table. How can these be shown?

create table t (a INT) comment='comment on t';

mysql> show tables like 't';
+-----------------------+
| Tables_in_steffen (t) |
+-----------------------+
| t |
+-----------------------+

mysql> desc t;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

You can see the comments on table table_name by

show create table table_name;

You can see the tables' comments with SHOW TABLE STATUS;

But as I'm here... If a user has no rights on a database (just USAGE), shouldn't it then be left out at SHOW DATABASES? My webhoster's MySQL server does so, but I can't figure out how they did it.

I have problem in desc tables of all the databases
and store them in a string array for querying
individual databases and tables automatically.


-> Create statement
-> store all the queries in string
-> loop with in another loop (while)
-> call the queries
-> print the results from the table

however after the first loop
the the error occured:

Executed Query: show tables Result: | above600 * cd_id * cd_structure * cd_smiles * cd_formula * cd_molweight * cd_fp1 * cd_fp2 * cd_fp3 * cd_fp4 * cd_fp5 * cd_fp6 * cd_fp7 * cd_fp8 * cd_fp9 * cd_fp10 * cd_fp11 * cd_fp12 * cd_fp13 * cd_fp14 * cd_fp15 * cd_fp16


Error : java.sql.SQLException: Operation not allowed after ResultSet closed



can anyone can help me to solve the problem

objective: Describe all the tables of all the databases from MYSQL.




==================
stm1 = con.createStatement();
String[] s8={"show databases","show tables"};

if(ivjJComboBox_Driver.getSelectedIndex() == 0 && ivjJComboBox_URL.getSelectedIndex() == 0)
{
System.out.println("Selected Table from Database : "+ivjJComboBox_Database.getSelectedItem()+" is "+ivjJComboBox_Table.getSelectedItem());
// s3 = "select * from "+ivjJComboBox_Table.getSelectedItem()+" where (cd_molweight >='" + ivjJTextField_from_molwt.getText() + "' and cd_molweight <= '" + ivjJTextField_to_molwt.getText() + "') " + ivjJComboBox_and_molwt.getSelectedItem() + " (cd_id >= '" + ivjJTextField_from_cdid.getText() + "' and cd_id <='" + ivjJTextField_to_cdid.getText() + "')";
// System.out.println(s3);
}
for(int j=1;j<2;j++){
rs2 = stm1.executeQuery(s8[j]);
System.out.println("Executed Query: "+s8[j]);
System.out.print("Result:" );
String s6;
while(rs2.next()){
s6=rs2.getString(1);
System.out.print(" | "+s6);
rs3=stm1.executeQuery("desc "+s6);
while(rs3.next()){
String s7=rs3.getString(1);
System.out.print(" * "+s7);
}
}
System.out.println("");
}

}
catch(Exception exception1)
{
System.out.println("Error : " + exception1);
}

Add your own comment.