MySQL usually stores two types of data in it: actual data stored in the form of tables and views, and information about the structure of a database and its objects. Such information is known as Metadata.

For instance, whenever a user forgets certain information of a database or its objects, MySQL provides specific commands to retrieve the said information. There are actually three types of information, which you can retrieve from a MySQL database. They are as follows −

• Information about the result of queries − This includes the number of records affected by any SELECT, UPDATE or DELETE statement.

• Information about the tables and databases − This includes information related to the structure of the tables and the databases.

• Information about the MySQL server − This includes the status of the database server, version number, etc.

It is very easy to get all this information at the MySQL prompt, but while using PERL or PHP APIs, we need to call various APIs explicitly to obtain all this information.

Obtaining Database Info from MySQL Prompt

While accessing a MySQL server from MySQL prompt, which is a Command Prompt in Windows and a Terminal in Linux etc., any information regarding a database using following commands.

• SHOW DATABASES: This command is used to retrieve the list of all databases present in MySQL.

• SHOW TABLES: This command is used to display the list of tables present in a database.

• mysql -V: This command is used to provide the current version of MySQL installed in your system.

• DESC or DESCRIBE: This command is used to retrieve the structure or definition of a database table.

mysql -V Command

If you want to check the version of MySQL Server installed in your system, use the following mysql -V on Command Prompt or Terminal.

Note: You must remember that the command prompt must be run as an administrator in Windows.

Example

In here, we are checking the current version of MySQL server in command prompt −

C:WindowsSystem32> mysql -V

Output

As we can see the output below, the current MySQL server version is ”8.0.33” −

mysql  Ver 8.0.33 for Win64 on x86_64 (MySQL Community Server - GPL)

SHOW DATABASES Command

To list or retrieve the names of all the databases in MySQL, you can use the following SHOW DATABASES command after logging into MySQL server −

Note − This command will list both system databases and user-defined databases together. The user must identify their specific user-defined database all the data is stored in.

Example

In the following query, we are fetching list of databases that exists in the current MySQL server −

SHOW DATABASES;

Here, the first three rows are system databases and the last two rows are user-defined databases −

Database
information_schema
mysql
performance_schema
tutorials
tutorials_copy

SHOW TABLES Command

To list all the tables in a MySQL database, you can use the SHOW TABLES command after selecting a database with USE command −

Example

In the below query, we are selecting a database named ”Tutorials” −

USE Tutorials;

Now, let us use the SHOW TABLES to fetch all the names of tables present in the above database −

Show Tables;

Output

Following is the list of tables exist in the ”Tutorials” database −

Tables_in_tutorials
customers
employees
students

DESC Command

If we want to check the structure of a MySQL table, we need to use the DESC or DESCRIBE query. DESC is a shortcut for DESCRIBE query, but retrieves the same result.

Example

Here, we are fetching the structure of MySQL CUSTOMERS table −

DESC CUSTOMERS;

Following is the structure −

Field	Type	Null	Key	Default	Extra
ID	int	NO	PRI	NULL	auto_increment
NAME	varchar(20)	NO		NULL
AGE	int	NO		NULL
ADDRESS	char(25)	YES		NULL
SALARY	decimal(18,2)	YES		NULL

Obtaining the Number of Rows Affected by a Query

Let is now see how to obtain this information.

PERL Example

In DBI scripts, the affected row count is returned by the do( ) or by the execute( ) command, depending on how you execute the query.

# Method 1
# execute $query using do( )
my $count = $dbh->do ($query);
# report 0 rows if an error occurred
printf "%d rows were affectedn", (defined ($count) ? $count : 0);

# Method 2
# execute query using prepare( ) plus execute( )
my $sth = $dbh->prepare ($query);
my $count = $sth->execute ( );
printf "%d rows were affectedn", (defined ($count) ? $count : 0);

PHP Example

In PHP, invoke the mysql_affected_rows( ) function to find out how many rows a query changed.

$result_id = mysql_query ($query, $conn_id);
# report 0 rows if the query failed
$count = ($result_id ? mysql_affected_rows ($conn_id) : 0);
print ("$count rows were affectedn");

Listing Tables and Databases

It is very easy to list down all the databases and the tables available with a database server. Your result may be null if you don”t have the sufficient privileges.

Apart from the method which is shown in the following code block, you can use SHOW TABLES or SHOW DATABASES queries to get the list of tables or databases either in PHP or in PERL.

PERL Example

# Get all the tables available in current database.
my @tables = $dbh->tables ( );

foreach $table (@tables ){
   print "Table Name $tablen";
}

PHP Example

Try the following example to get database info −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Getting MySQL Database Info</title>
   </head>
   <body>
      <?php
         $dbhost = ''localhost'';
         $dbuser = ''root'';
         $dbpass = ''root@123'';
         $dbname = ''TUTORIALS'';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         $tutorial_count = null;
		 
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf(''Connected successfully.<br />'');
		 
         if ($result = mysqli_query($mysqli, "SELECT DATABASE()")) {
            $row = mysqli_fetch_row($result);
            printf("Default database is %s<br />", $row[0]);
            mysqli_free_result($result);
         }
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.
Default database is tutorials

Getting Server Metadata

There are a few important commands in MySQL which can be executed either at the MySQL prompt or by using any script like PHP to get various important information about the database server.

Sr.No.	Command & Description
1	SELECT VERSION( ) Server version string
2	SELECT DATABASE( ) Current database name (empty if none)
3	SELECT USER( ) Current username
4	SHOW STATUS Server status indicators
5	SHOW VARIABLES Server configuration variables