I am creating a report to identify the maximum batch_id for each table and table schema across our databases.
I want it to look something like this:
| table_schema | table_name | column_name | maximum_batch_id |
|---|---|---|---|
| Schema_1_Name | Table_Name_1 | batch_id | 1252753 |
| Schema_1_Name | Table_Name_2 | batch_id | 1252753 |
| Schema_2_Name | Table_Name_1 | batch_id | 1252757 |
I can get the information_schema information for table_schema, table_name and column_name using:
select table_schema, table_name, column_name from information_schema.columns where column_name = 'batch_id'
I could not figure out a way to get the batch_id value without a lot of unions. If there is a better way, please let me know.
How do I get the table_schema, table_name and column_name to be included with my results? Do I have to hard code them or is there an easier way?
Thank you in advance.