How can tables in a mysql database be counted?

I'm using MySQL v.5.0.77 and I was wondering if it is possible to count the number of tables in a database. I have done a lot of research and am unable to find a way to do this that is not depreciated.

For each user that signs up I had generated a table in a database fo开发者_运维百科r them. And now I am trying to get a live count of those tables, if that makes sense. Is this a logical way of storing user information? I am unable to programmatically create entire databases on my server.

---

You can do a query to information_schema.tables

SELECT COUNT(*) FROM information_schema.TABLES
WHERE TABLE_SCHEMA='$database_name';

---

You could do it like this:

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='YOUR_DB_NAME_HERE'

• http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

---

For each user that signs up I had generated a table in a database for them. And now I am trying to get a live count of those tables, if that makes sense. Is this a logical way of storing user information?

If you're creating a separate table for each and every user then probably not. There are better ways to store the data and relationships, which can be more complicated to learn but will provide far more flexibility, abilities, and scalability later on.

I think you're probably trying to reproduce the functionality of the database in your programming e.g. getting a list of users would require you to run a query on every single user table.

I recommend you take a look at database design and database normalization (try to focus on the concept of how best to store data first without getting bogged down in the specifics).

---

I don't know if this is slower than ajreal's answer but I've been using :

$sql = "SHOW TABLES";
$res = mysql_query($sql);
$num_tables = mysql_num_rows($res); 

---

Open Terminal, and type use myDB, then show tables; It will gives you total tables name and last line as total tables count 47 rows in set (0.00 sec)

Or type below query

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='myDB'