Geek Answers Handbook

How to count tables in mysql database?

I am using MySQL v. 5.0.77 , and I was wondering if it is possible to count the number of tables in the database. I did a lot of research and could not find a way to do this, which does not depreciate.

For each user who subscribes, I created a table in the database for them. And now I'm trying to count the number of these tables, if that makes sense. Is this a logical way to store user information? I cannot programmatically create entire databases on my server.

+4
source share
5 answers

You can request information_schema.tables

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

You can do it as follows:

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

For each user who subscribes, I created a table in the database for them. And now I'm trying to count the number of these tables, if that makes sense. Is this a logical way to store user information?

If you create a separate table for each user, then probably not. There are more efficient ways to store data and relationships that may be more difficult to learn, but will provide much greater flexibility, capabilities, and scalability in the future.

I think you are probably trying to reproduce the database functionality in your programming, for example. to get a list of users, you need to run a query for each individual user table.

I recommend that you take a look at database normalization (try to focus on the concept of how best to store data without clogging up in details).

+2
source

I don't know if this is slower than the ajreal answer, but I used:

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

Open a terminal and type use myDB , then show tables; This will give you the common table name and last row, since the total number of tables is 47 rows in set (0.00 sec)

Or enter below request

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

More articles:


All Articles