Recipe 9.9. Getting Server Metadata

Problem

You want the MySQL server to tell you about itself.

Solution

Several SQL functions and SHOW statements return information about the server.

Discussion

MySQL offers several SQL functions and statements that provide you with information about the server itself and about your current client connection. A few that you may find useful are listed here. To obtain the information provided by any of them, issue the statement, and then process its result set. Both SHOW statements allow a LIKE ' pattern ' clause for limiting the results only to those rows matching the pattern.

Statement	Information produced by statement
SELECT VERSION⁠(⁠ ⁠ ⁠)	Server version string
SELECT DATABASE⁠(⁠ ⁠ ⁠)	Default database name (NULL if none)
SELECT USER⁠(⁠ ⁠ ⁠)	Current user as given by client when connecting
SELECT CURRENT_USER⁠(⁠ ⁠ ⁠)	User used for checking client privileges
SHOW GLOBAL STATUS	Server global status indicators
SHOW VARIABLES	Server configuration variables

A given API might provide alternative ways to access these types of information. For example, JDBC has several database-independent methods for obtaining server metadata. Use your connection object to obtain the database metadata, and then invoke the appropriate methods to get the information in which you're interested. You should consult a JDBC reference for a complete list, but here are a few representative examples:

DatabaseMetaData md = conn.getMetaData (); // can also get this with SELECT VERSION() System.out.println ("Product version: " + md.getDatabaseProductVersion ()); // this is similar to SELECT USER() but doesn't include the hostname System.out.println ("Username: " + md.getUserName ());