MySQL sQuery draft

[SFrav]

I'm not sure how generalisable this is, but here's my attempt at converting the postgres sQuery to MySQL. I think there is something wrong with my foreign key references. There was no table equivalent to information_schema.constraint_column_usage in my database, so I cobbled three tables together that seem to have the same information.

Posting here in case others find it useful. Tested on MariaDB. Note: you may have to change c.TABLE_SCHEMA = 'public' to suit your needs. Also, wherever you see code highlighting, you will need to surround the word with backticks '`'.

sQuery <- "select

t.TABLE_NAME as table, c.COLUMN_NAME as column, case when pk.COLUMN_NAME IS NULL then 0 else 1 end as key, fk.ref, fk.ref_col, case c.IS_NULLABLE when 'YES' then 0 else 1 end as mandatory, c.DATA_TYPE as type, c.ORDINAL_POSITION as column_order

from information_schema.columns c inner join information_schema.tables t on t.TABLE_NAME = c.TABLE_NAME and t.TABLE_CATALOG = c.TABLE_CATALOG and t.TABLE_SCHEMA = c.TABLE_SCHEMA

left join -- primary keys ( SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, kcu.COLUMN_NAME FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.CONSTRAINT_NAME WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' ) pk on pk.TABLE_NAME = c.TABLE_NAME and pk.COLUMN_NAME = c.COLUMN_NAME

left join -- foreign keys ( SELECT tc.CONSTRAINT_NAME, kcu.TABLE_NAME, kcu.COLUMN_NAME, ccu.TABLE_NAME as ref, ccu.FOR_COL_NAME as ref_col FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
JOIN (SELECT foreignTab.ID, cons.TABLE_NAME, fCol.FOR_COL_NAME, cons.CONSTRAINT_NAME FROM (select ROW_NUMBER() OVER () AS row_num, CONSTRAINT_NAME, TABLE_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS) as cons left join (SELECT ID, ROW_NUMBER() OVER ()-1 AS row_num, SUBSTRING_INDEX(FOR_NAME, '/', -1 ) AS TABLE_NAME FROM information_schema.INNODB_FOREIGN) AS foreignTab on cons.row_num = foreignTab.row_num AND cons.TABLE_NAME = foreignTab.TABLE_NAME left join information_schema.INNODB_FOREIGN_COLS as fCol on foreignTab.ID = fCol.ID) AS ccu ON ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME AND ccu.TABLE_NAME = tc.TABLE_NAME WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY' ) fk on fk.TABLE_NAME = c.TABLE_NAME and fk.COLUMN_NAME = c.COLUMN_NAME

where c.TABLE_SCHEMA = 'public' and t.TABLE_TYPE = 'BASE TABLE'"