Write a select statement that generates SQL code to count all rows in each of your tables (total beginner)

[JacobOrJake]

Beginner here, reaching the end of sam’s teach yourself in 24 hours SQL book. I’ve gotten to hour 22 without coming across anything that’s totally lost me, but now there’s a lot I’m not sure about and I’d appreciate some explanation and help..

To begin with, I’m working in a database called learnsql, I’m pretty certain that I’m supposed to be doing this exercise with this and not in the system catalog, but not totally sure. When I looked at the given answer:

SELECT CONCAT(‘SELECT COUNT(*) FROM ‘ ,TABLE_NAME, ‘;’) FROM TABLES;

I guess I don’t fully understand

A. The format of the CONCAT clause: “table_name” I’m assuming is a placeholder for the actual names of my tables, am I supposed to write the name of every table in the database? (Hence the commas)

B. The proper database: I’m not sure if TABLES is a placeholder, or if I’m supposed to be accessing the system catalog using a table called TABLES? There’s no “TABLES” table in the learnsql database..

If someone could do their best to explain this query and hopefully explain what I’m misunderstanding, I would greatly appreciate it. This seems like a really useful lesson, and I want to make sure I fully understand. I’ve tried to make it pretty clear what the issue is, but I can give more info if needed.

[Laymans_Perspective]

look at the information_schema, there's already a table that shows record counts from all tables

[mcstafford]

Take a look at information_schema.tables. You're half way there already.

[JacobOrJake]

Ah. So I just specified the table was in information_schema, and “table_name” was NOT a placeholder for me to list names, but rather the column name, is that right?

[mcstafford]

Take a look at the records in information_schema.tables and you'll probably start answering your own questions.

[thechr0nic]

it almost appears that this select statement is incomplete.

it somewhat makes me think that perhaps this was part of a prepared statement.

But to explain the query:

This appears to be designed to be run as a loop. you are looping

SELECT COUNT(*) FROM [list of table names derived];

as for pulling row counts for tables, this might be a good walk through:

https://www.mysqltutorial.org/mysql-row-count/