MySQL显示表的数据字典 [英] MySQL Show Datadictionary of tables

问题描述

我想显示数据库中整个表的DataDictionary.

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

推荐答案

在以下情况下，这里是我用来生成数据字典的内容:

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

这将列出服务器上的所有数据库.您可能需要更改where子句，以仅查看所需的特定表模式.

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

这篇关于MySQL显示表的数据字典的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！