Determining the number of tables that are contained in a MySQL database is very straight-forward, although it is an often asked question. The simplest way to accomplish this is using the following SQL query. In this query, you will provide the database, and the SQL will access MySQL’s internal data scheme (information_schema).
SELECT count(*) as ‘Tables’, table_schema as ‘Database’
FROM information_schema.TABLES
WHERE table_schema= ‘The Database Name’
GROUP BY table_schema
If you need to retrieve this information using PHP, use can use the following code. It creates a connection, runs the query, and returns the number of rows retrieved. This query you notice is slightly different than the one above, often there are multiple ways to get the same result!
$conn = mysql_connect(‘localhost’, ‘USERNAME’, ‘PASSWORD’, 1, 65536);
$res = mysql_query( “select table_name from information_schema.tables where table_schema=’test’”, $conn );
echo mysql_num_rows( $res );
And lastly, there is one more way that you can retrieve the number of tables in a database using PHP and the Show Tables command. In this example, you will need to be connected to a server, and have the database set to the one you are querying against.
echo “<pre>”;
$tbl_List = mysql_query(“SHOW TABLES”);
$i=0;
while($tables = mysql_tablename($tbl_List,$i))
{
echo $tables;
$i++;
}
echo “<br />Table count = $i”;
echo “</pre>”;
Related articles
- Tmobile Comet (neuphones.com)
- Bell P-39N Airacobra – Little Sir Echo – Small Fry (classic-machines.com)
- Topgun Days by Dave “Bio” Baranek – The Truth Behind Topgun and Top Gun (classic-machines.com)
