MySQL Select All Fields of ALL Tables which have records Order By Table

Question

I am in need of a query to select all Tables, field names, from all tables where table has data.

I would like to be able to have Table, Field where given table has data rows in my result set.

I looked at this .... Getting MySQL Schemas for All Tables

and this ... How can I find all the tables in MySQL with specific column names in them?

I believe I need something similar to

SELECT COL.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS COL
WHERE COL.TABLE_NAME IN

(SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME LIKE '%tbl%')

....    
Having Count > 0

I don't know what dynamic table assignment has to do with the question. You state This question already has answers here: Dynamic table name at sql statement Nothing to do with the question. Tushar ha sit right . — Ken, Mar 08 '23 at 13:58
I agree too. Do we have to flag moderator for `someone marking it as a duplicate of a totally different question` ? — Tushar, Mar 08 '23 at 14:09

Tushar · Accepted Answer · 2023-03-08T13:28:05.370

The following query shall give you the tables from 'your_schema_name' along with it's row count

select
  table_Name, SUM(table_rows)
from information_schema.tables
where table_schema = 'your_schema_name'
group by table_Name;

To Simply select all tables which have atleast one record; Please use below query :

select table_name
from information_schema.tables
where table_schema = 'your_schema_name'
and table_rows > 0;

Also; To view fields (columns) from all the tables which have data; You can use following query :

select table_Name, column_name
from information_schema.columns 
where table_schema = 'your_schema_name'
and table_name in (
       select table_name 
       from information_schema.tables
       where table_schema = 'your_schema_name' 
       and table_rows > 0
   );

OR; You can also write the above query using (inner) join as :

select
  i.table_Name, 
  i.column_name
from
  information_schema.columns i 
  inner join information_schema.tables t 
  on i.table_name= t.table_Name
where
  i.table_schema = 'your_schema_name' 
  and t.table_schema = 'your_schema_name' 
  and t.table_rows > 0;

I was playing around with the thing SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'mydb' AND TABLE_ROWS > 0) ORDER BY TABLE_NAME I will mark yours as answer! — Ken, Mar 08 '23 at 13:56

MySQL Select All Fields of ALL Tables which have records Order By Table

1 Answers1