Getting list of table columns with their foreign keys
Posted by: Mohsen Omidi
Date: October 04, 2020 02:00AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Getting list of table columns with their foreign keys
129
October 04, 2020 02:00AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.