Lberteh Lberteh - 1 month ago 4
MySQL Question

mySQL, select BOTH table_rows AND column_name from information_schema

I was asked to CREATE A PROCEDURE that lists TABLE_NAME, TABLE_ROWS, COLUMN_NAME and DATA_TYPE from a db.

Problem is, I can only select column_name using INFORMATION_SCHEMA.COLUMNS and I can select table_rows using INFORMATION_SCHEMA.table. I tried to join both but no success.

Also, I need to filter for VARCHAR columns with a CHARACTER_MAXIMUM_LENGTH > 5
, but if I go "WHERE CHARACTER_MAXIMUM_LENGTH > 5" it will get not only varchar but also char data_types as well.

EDIT
Here is what I have so far

SELECT c.table_name, c.column_name, c.data_type, t.table_rows, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.columns c
JOIN information_schema.tables t ON c.table_name = t.table_name
WHERE t.table_schema = 'SAMPLE' AND CHARACTER_MAXIMUM_LENGTH > 5;


I think I got the proper join to work, but I'm still not sure how to get that "CHARACTER_MAXIMUN_LENGHT > 5" to only filter varchar data types...

Any thoughts?

Answer

Exclude all data types except of varchar from your condition:

AND (CHARACTER_MAXIMUM_LENGTH > 5 OR DATA_TYPE <> 'varchar')