Last Updated: September 01, 2020
·
274
· hunterae

Mysql - Select tables based on column constraints

A coworker showed me that you can query entire schemas based on column constraint information. Today, we were attempting to fetch the names of all database tables in our mobilecausedevelopment database that have createdat and updatedat fields that are nullable. This can be accomplished by querying the INFORMATIONSCHEMA database and the COLUMNS table. Here's the query:

SELECT `TABLE_NAME`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `COLUMN_NAME` = 'created_at'
AND `TABLE_SCHEMA` = 'mobilecause_development'
AND `IS_NULLABLE` = 'YES' ;