The following is a MYSQL query that displays the structure of all the columns in all the databases of the localhost:
copyraw
SELECT * FROM information_schema.COLUMNS ORDER BY TABLE_NAME, COLUMN_NAME
- SELECT * FROM information_schema.COLUMNS ORDER BY TABLE_NAME, COLUMN_NAME
The following is a MYSQL query that finds all columns (displayed as "tablename.columnname") that had the data_type TEXT across all databases:
copyraw
SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' ORDER BY TABLE_NAME, COLUMN_NAME
- SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' ORDER BY TABLE_NAME, COLUMN_NAME
The following is a MYSQL query that finds all columns that had the data_type TEXT across a specified database (eg. sample_db):
copyraw
SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' AND TABLE_SCHEMA='sample_db' ORDER BY TABLE_NAME, COLUMN_NAME
- SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' AND TABLE_SCHEMA='sample_db' ORDER BY TABLE_NAME, COLUMN_NAME
Note that the user launching this SQL query would need the SELECT privilege as a minimum on "information_schema" (arguable).
Category: MySQL :: Article: 305
