Check the Schema, tables and column information in MySQL

Check the Schema, tables and column information in MySQL

Check the list of schema/databases

show schemas;
OR
select schema_name as database_name from information_schema.schemata order by schema_name;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test10             |
+--------------------+

Check the tables present in MySQL Database

show tables;

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t              |
| test           |
| test1          |
| test3          |
+----------------+

Check table type with Show command

SHOW FULL TABLES

+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| t              | BASE TABLE |
| test           | BASE TABLE |
| test1          | BASE TABLE |
| test3          | BASE TABLE |
+----------------+------------+

Check tables detail with Like and Where operator

SHOW TABLES LIKE pattern;
SHOW TABLES WHERE expression;

mysql> SHOW TABLES LIKE 'te%';
+----------------------+
| Tables_in_test (te%) |
+----------------------+
| test                 |
| test1                |
| test3                |
+----------------------+

SHOW FULL TABLES WHERE table_type = 'BASE TABLE';

mysql> SHOW FULL TABLES WHERE table_type = 'BASE TABLE';
+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| t              | BASE TABLE |
| test           | BASE TABLE |
| test1          | BASE TABLE |
| test3          | BASE TABLE |
+----------------+------------+

Check the structure of Table

DESCRIBE test;
mysql> DESCRIBE test;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+

Check the columns of table

SHOW COLUMNS FROM table_name;
SHOW COLUMNS FROM database_name.table_name;

mysql> SHOW COLUMNS FROM test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

SHOW FULL COLUMNS FROM table_name;

mysql> SHOW FULL COLUMNS FROM test;
+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type    | Collation | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
| id    | int(11) | NULL      | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+

-- Use of like or where operator
SHOW COLUMNS FROM table_name LIKE pattern;
SHOW COLUMNS FROM table_name WHERE expression;

Following view help in finding more information:

--table detail
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='MYDATABASE';

--Column detail
SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS;

Advertisements

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.