Working with Metadata - MySQL - page 355

def database_exists ( dbh , db_name )
return ! dbh . select_one ( "SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = ?" , db_name ) . nil?
end
To obtain the list of tables in a database, name the database in the WHERE clause of a
statement that selects from the TABLES table:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA . TABLES
WHERE TABLE_SCHEMA = 'cookbook' ;
To sort the result, add an ORDER BY TABLE_NAME clause.
To obtain a list of tables in the default database, use this statement instead:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA . TABLES
WHERE TABLE_SCHEMA = DATABASE ();
If no database has been selected, DATABASE() returns NULL and no rows match, which
is the correct result.
To check whether a specific table exists, use a WHERE clause with a condition that names
the table. Here's a Ruby method that performs an existence test for a table in a given
database:
def table_exists ( dbh , db_name , tbl_name )
return ! dbh . select_one ( "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?" ,
db_name , tbl_name ) . nil?
end
Some APIs provide a database-independent way to get database or table lists. In Perl
DBI, the database handle tables() method returns a list of tables in the default database:
@tables = $dbh -> tables ();
The Ruby method is similar:
tables = dbh . tables
For Java, there are JDBC methods designed to return lists of databases or tables. For
each method, invoke your connection object's getMetaData() method and use the
resulting DatabaseMetaData object to retrieve the information you want. Here's how to
produce a list of databases:
// get list of databases
DatabaseMetaData md = conn . getMetaData ();
ResultSet rs = md . getCatalogs ();
while ( rs . next ())
System . out . println ( rs . getString ( 1 )); // column 1 = database name
rs . close ();
To list the tables in a database, do this: