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.
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;
Exclude all data types except of
varchar from your condition:
AND (CHARACTER_MAXIMUM_LENGTH > 5 OR DATA_TYPE <> 'varchar')