Thursday, 28 October 2010 11:36
The following is a MYSQL query that displays the structure of all the columns in all the databases of the localhost:
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:
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):
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).
| < Prev | Next > |
|---|
