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.
CodePudding user response：
select sum(row_count) as totalRecords
from(
select table_schema,
table_name,
(xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count
from (
select table_name, table_schema,
query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
from information_schema.tables t
join tableA ta on ta.tablename = t.table_name
)t
) t
CodePudding user response：
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:
select 'TABLE_1', count(*) from table_1 having 'TABLE_1' in (select tablename from tablea)
union all
select 'TABLE_2', count(*) from table_2 having 'TABLE_2' in (select tablename from tablea)
union all
select 'TABLE_3', count(*) from table_3 having 'TABLE_3' in (select tablename from tablea)
union all
select 'TABLE_4', count(*) from table_4 having 'TABLE_4' in (select tablename from tablea)
union all
select 'TABLE_5', count(*) from table_5 having 'TABLE_5' in (select tablename from tablea)
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.