I have a table in which one column represent a name of a table in my db.
TableA
| tableName |
-------------
| table 1 |
| table 2 |
| ....... |
| table n |
What I need to do is to count all the records that are inside each table listed in tableA.
What I managed to do so far is this:
select count(*)
from (
select tableName
from tableA
) tables
but this is wrong because it counts the number of rows in the tableA table, which is not what I need.
The list of table don’t have any relationship pretty much so there are no join operations, I just need to add to a counter the number of all records in each table.
Can that be done with a plain sql query? I’m using postgresql but would like to come up with a solution that doesn’t depend on any db vendor.
2
Answers
A pure SQL solution that works in about any DBMS? Well, as you know you cannot simply query the table names from your control table and use these in a SQL FROM clause, because the table names in the control table are data while the table names in the FROM cause are structure, so to say.
As you should know your database, however, you can use the table names known to you and compare them to the entries in your control table:
This query must be altered when new tables get added to the database. As adding tables to a database is something that happens seldom if ever, this shouldn’t be an issue.
Demo: https://dbfiddle.uk/Dfk9nIFo