CODEWITHSUNDEEP
mysql
SiHyung Lee
SiHyung Lee

Reputation: 349

MySQL Show Datadictionary of tables

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

Upvotes: 6

Views: 23072

Answers (7)

pbarney
pbarney

Reputation: 2833

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.

Upvotes: 9

Hugo González
Hugo González

Reputation: 1

I like this one, it's simple with elemental info.

SELECT
    table_name,
    column_name,
    column_type,
    is_nullable,
    column_comment 
FROM
    information_schema.COLUMNS 
WHERE
    table_schema = 'YOUR_SCHEMA_NAME' 
ORDER BY
    table_name,
    ordinal_position ASC;

Upvotes: 0

chb
chb

Reputation: 2005

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.

Upvotes: 2

Nick Dong
Nick Dong

Reputation: 3736

describe table_name;

Retrieving information from INFORMATION_SCHEMA, but need DBA privilege.

Upvotes: 1

simhumileco
simhumileco

Reputation: 34563

The shortest syntax is this:

SHOW COLUMNS 
FROM `MyDataBase`.`MyTables`;

Full SHOW Syntax for columns:

SHOW [FULL] COLUMNS 
FROM tbl_name [FROM db_name] 
[like_or_where]

Upvotes: 0

georgecj11
georgecj11

Reputation: 1637

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

Upvotes: 1

Vikram
Vikram

Reputation: 8333

is this what you want:

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

Upvotes: 4

Related Questions