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'"
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_usagein 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 '`'.