Posted in dbms, mysql

[mysql] Show all column from all tables

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




Hope it helps all the developers who have same problem

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s