Tuesday, December 7, 2010

MySql - Get available Database, Tables, Columns and their Details

Get available database and their information: 
select * from INFORMATION_SCHEMA.SCHEMATA; Result Set Columns:
  1. CATALOG_NAME
  2. SCHEMA_NAME
  3. DEFAULT_CHARACTER_SET_NAME
  4. DEFAULT_COLLATION_NAME
  5. SQL_PATH
Note: SCHEMA_NAME column contains the database names as you need.

Get available table names from a database:
select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='test_db';

Result Set Columns:
  • TABLE_NAME contains the table name
Get table column details:
select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLUMN_KEY, EXTRA from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='test_db' and TABLE_NAME='user_profile';
If you want checkout INFORMATION_SCHEMA database inside MySql for more.

No comments:

Post a Comment