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;
Smart way of Technology 