Need some help trying to solve this issue. I have a query from the database itself to get me the table_schema names and create a row number:
SELECT ROW_NUMBER() OVER (ORDER BY TABLE_SCHEMA), TABLE_SCHEMA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA like 'test\_%'
ORDER BY TABLE_SCHEMA
And I want to create a while loop with this query to give me data from each table with this query:
Select
count('list'),
name
From test_123.order
group by name
So something like this pseudo code:
While row_number less than total rows
Select
count('list'),
name
From concat((SELECT ROW_NUMBER() OVER (ORDER BY TABLE_SCHEMA), TABLE_SCHEMA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA like 'test\_%'
ORDER BY TABLE_SCHEMA),'.order')
group by name
In the end I want to get this:
| ROW_NUMBER | TABLE_SCHEMA | name | count('list') |
|---|---|---|---|
| 1 | test_123 | Jack | 100 |
| 2 | test_234 | Ripper | 200 |
Is this possible to achieve?
Kind regards
