Sometime when we work on a large database structure that contains many tables, we don’t know the relationship between tables. If the architect never write any documentation, we may have difficulties to trace the relationship.
Luckily, most of the Database system allow us to query the columns and tables name in the database.
Following is the SQL script to select all tables from a MySQL database.
select * from information_schema.columns where table_schema = ‘l2gs’
order by table_name,ordinal_position
So when we are frustrated on how a table is related to another table, we can easily filter by the column name, hopefully the database architect create the same column name for the related table
select * from information_schema.columns where table_schema = ‘l2gs’and column_name like ‘%REASON%’ order by table_name,ordinal_position