Bookmark and Share

Querying database structure.

Posted: Thursday, January 15th, 2009 at 11:27 pmUpdated: Friday, January 30th, 2009 at 10:09 pm

There are times when we want to query a database structure for a particular table name. Or you want to find which tables a particular column name is defined. Or in my case, I wanted to find all tables that has column A and column B defined … how do I do it?

If you are using InnoDB of MySQL

You can use database called information_schema. It has all the information about table structure that you need. For example, if you want to find out what tables has column user_id. You would run the SQL query below and it’ll return you all the tables that has user_id column.

SELECT
   TABLE_SCHEMA, TABLE_NAME
FROM
   `information_schema`.`COLUMNS`
WHERE
   COLUMN_NAME = 'user_id'

Take a look at the structure of information_schema database and the tables it contains. It should be pretty easy to figure out what SQL code you need to run to get the information that you need.

I hope this article helps you. As always, I welcome comments / questions / critics that will help me and other readers understand better.

Leave a Reply