SHOW DATABASES & SHOW TABLES

We can display the entire database present in the MySQL by using query SHOW DATABASES. Based on your login privilege we can display the list of database.
SHOW DATABASES
We will try to display a drop down list box with all database names as options. We will use PHP & PDO with database connection to display list of databases. Here is the code.
<?Php
require "config.php"; // Database Connection

$result = $dbo->query("SHOW DATABASES");

echo "<select name=d1>";
while ($row = $result->fetch(PDO::FETCH_NUM)) {
echo "<option value='$row[0]'>$row[0]</option>";
}
echo "</select>";
?>

Show Tables of the database

We can list out all the tables present inside a database by using SHOW TABLES query. Here is the example of listing all the tables of a database.
<?Php
require "config.php"; // Database Connection

$result = $dbo->query("SHOW TABLES");

while ($row = $result->fetch(PDO::FETCH_NUM)) {
echo $row[0]."<br>";
}
?>

Using LIKE query

We can filter tables based on the part of the name by using LIKE query.
show tables where Tables_in_sql_tutorial like 'analytics_%' 
this will display all tables ( names ) starting with analytics_ inside the database sql_tutorial

OR
SHOW TABLES  like 'analytics_%'

Using schema and order by

We can display in order of table names by using order by query. We can also include Like command to filter the tables.
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'sql_tutorial'
  ORDER BY table_name ASC
SQL References Primary Key constraint Create Table
Copy Table & SHOW CREATE Table
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer