missing-fk.sql

-- Replace INNODB_FOREIGN with INNODB_SYS_FOREIGN and INNODB_FOREIGN_COLS with INNODB_SYS_FOREIGN_COLS for older MySQL versions
SELECT 
    columns.table_name, engine, columns.column_name
FROM
    information_schema.columns
        INNER JOIN
    information_schema.tables ON tables.table_name = columns.table_name
        AND table_type = 'BASE TABLE'
WHERE
    NOT EXISTS( SELECT 
            *
        FROM
            information_schema.INNODB_FOREIGN f
                LEFT JOIN
            information_schema.INNODB_FOREIGN_COLS fc ON f.ID = fc.ID
        WHERE
            FOR_NAME = CONCAT(columns.table_schema,
                    '/',
                    columns.table_name)
                AND FOR_COL_NAME = column_name)
        AND (`column_name` LIKE '%\_id'
        OR column_name LIKE '%\_by')
        AND tables.TABLE_SCHEMA = DATABASE()
ORDER BY tables.table_name