Getting list of table columns with their foreign keys

[Mohsen Omidi]

I am trying to get the table information (List table columns with their foreign keys in MySQL database) and I try to execute the query below from MySql database metadata schema `information_schema`:

SELECT
`t`.`TABLE_SCHEMA` AS `table_schema`,
`t`.`TABLE_NAME` AS `table_name`,
`t`.`COLUMN_NAME` AS `column_name`,
IF
(( `k`.`COLUMN_NAME` IS NULL ), 'NO', 'YES' ) AS `indexed`,
IF
(( `tb`.`REFERENCED_TABLE_NAME` IS NOT NULL ), 'YES', 'NO' ) AS `has_foriegn_key`,
`tb`.`REFERENCED_TABLE_SCHEMA` AS `forign_schema`,
`tb`.`REFERENCED_TABLE_NAME` AS `forign_table`,
`tb`.`REFERENCED_COLUMN_NAME` AS `forign_column`
FROM
((
information_schema.`COLUMNS` `t`
LEFT JOIN information_schema.`KEY_COLUMN_USAGE` `tb` ON (((
`t`.`TABLE_NAME` = `tb`.`TABLE_NAME`
)
AND ( `t`.`TABLE_SCHEMA` = `tb`.`TABLE_SCHEMA` )
AND ( `t`.`COLUMN_NAME` = `tb`.`COLUMN_NAME` ))))
LEFT JOIN information_schema.`STATISTICS` `k` ON (((
`k`.`COLUMN_NAME` = `t`.`COLUMN_NAME`
)
AND ( `k`.`TABLE_SCHEMA` = `t`.`TABLE_SCHEMA` )
AND ( `k`.`TABLE_NAME` = `t`.`TABLE_NAME` ))));

each row shows the `table_schema, table_name, column_name , etc.. `
if each column had the foreign key I expect the query shows the related parent column information in these columns `forign_schema, forign_schema, forign_schema` but the query will not work !

if I put this where clause (`WHERE t.TABLE_NAME = 'SOME TABLE NAME WITH FOREIGN KEY'`) in query it return the data correctly like below :


SELECT
`t`.`TABLE_SCHEMA` AS `table_schema`,
`t`.`TABLE_NAME` AS `table_name`,
`t`.`COLUMN_NAME` AS `column_name`,
IF
(( `k`.`COLUMN_NAME` IS NULL ), 'NO', 'YES' ) AS `indexed`,
IF
(( `tb`.`REFERENCED_TABLE_NAME` IS NOT NULL ), 'YES', 'NO' ) AS `has_foriegn_key`,
`tb`.`REFERENCED_TABLE_SCHEMA` AS `forign_schema`,
`tb`.`REFERENCED_TABLE_NAME` AS `forign_table`,
`tb`.`REFERENCED_COLUMN_NAME` AS `forign_column`
FROM
((
information_schema.`COLUMNS` `t`
LEFT JOIN information_schema.`KEY_COLUMN_USAGE` `tb` ON (((
`t`.`TABLE_NAME` = `tb`.`TABLE_NAME`
)
AND ( `t`.`TABLE_SCHEMA` = `tb`.`TABLE_SCHEMA` )
AND ( `t`.`COLUMN_NAME` = `tb`.`COLUMN_NAME` ))))
LEFT JOIN information_schema.`STATISTICS` `k` ON (((
`k`.`COLUMN_NAME` = `t`.`COLUMN_NAME`
)
AND ( `k`.`TABLE_SCHEMA` = `t`.`TABLE_SCHEMA` )
AND ( `k`.`TABLE_NAME` = `t`.`TABLE_NAME` ))))
WHERE t.TABLE_NAME = 'SOME TABLE NAME WITH FOREIGN KEY';


it seems something wrong with the join behavior.