MySQL Show Datadictionary of tables

Question

I'd like to show DataDictionary for entire tables in database.

SHOW COLUMNS
FROM `MyDataBase`.`MyTables` 
WHERE IN ( SELECT TABLE_NAME 
           FROM information_schema.TABLES 
           WHERE TABLE_SCHEMA = 'MyDataBase'
);

Can i use query something like this? I want to see entire column data using a single query

Answer 1

Here is what I use to generate a data dictionary when I have to:

SELECT t.table_schema AS db_name,
       t.table_name,
       (CASE WHEN t.table_type = 'BASE TABLE' THEN 'table'
             WHEN t.table_type = 'VIEW' THEN 'view'
             ELSE t.table_type
        END) AS table_type,
        c.column_name,
        c.column_type,
        c.column_default,
        c.column_key,
        c.is_nullable,
        c.extra,
        c.column_comment
FROM information_schema.tables AS t
INNER JOIN information_schema.columns AS c
ON t.table_name = c.table_name
AND t.table_schema = c.table_schema
WHERE t.table_type IN ('base table', 'view')
AND t.table_schema LIKE '%'
ORDER BY t.table_schema,
         t.table_name,
         c.ordinal_position

This will list all of the databases on the server that the logged in user has access to. You may want to change the where clause to only look at the specific table schema you want.

Answer 2

is this what you want:

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' 
    AND `TABLE_NAME`='yourtablename';

Answer 3

From MySQL 5.7 Manual

Many sections indicate what SHOW statement is equivalent to a SELECT that retrieves information from INFORMATION_SCHEMA. For SHOW statements that display information for the default database if you omit a FROM db_name clause, you can often select information for the default database by adding an AND TABLE_SCHEMA = SCHEMA() condition to the WHERE clause of a query that retrieves information from an INFORMATION_SCHEMA table.

Answer 4

Usually I prefer to take this with multiple DESC. I feel SHOW COLUMNS is bit slower than DESC table_name.

So if want to get all the columns in some databases

1. Loop thru SHOW TABLES FROM DB_NAME
2. Loop thru all tables as DESC table_name

In the same way, SHOW INDEXES is slower when compared to SHOW CREATE TABLE if you just want to see the indexes on a table