【小白專用】MySQL查詢數據庫所有表名及表結構其注釋

一、先了解下INFORMATION_SCHEMA
1、在MySQL中，把INFORMATION_SCHEMA看作是一個數據庫，確切說是信息數據庫。其中保存著關于MySQL服務器所維護的所有其他數據庫的信息。如數據庫名，數據庫的表，表欄的數據類型與訪問權限等。在INFORMATION_SCHEMA中，有數個只讀表。它們實際上是視圖，而不是基本表，因此，你將無法看到與之相關的任何文件。

2、TABLES表：提供了關于數據庫中的表的信息(包括視圖)。詳細表述了某個表屬于哪個schema，表類型，表引擎，創建時間等信息。是show tables from schemaname的結果取之此表。

3、COLUMNS表：提供了表中的列信息。詳細表述了某張表的所有列以及每個列的信息。是show columns from schemaname.tablename的結果取之此表。
?

查看ftp數據庫內以oemp開頭的所有的表名、表數據量、表備注、字段名稱、字段類型、默認值、字段備注等；如果查整個數據庫就把ftp后全刪除。

            string sql = $@"SELECT TABLE_NAME as TableName, column_name AS DbColumnName,CASE WHEN  left(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)-1)='' THEN COLUMN_TYPE ELSE  left(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)-1) END AS DataType,CAST(SUBSTRING(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)+1,LOCATE(')',COLUMN_TYPE)-LOCATE('(',COLUMN_TYPE)-1) AS signed) AS Length,column_default  AS  `DefaultValue`,column_comment  AS  `ColumnComment`,CASE WHEN COLUMN_KEY = 'PRI' THEN true ELSE false END AS `IsPrimaryKey`,CASE WHEN EXTRA='auto_increment' THEN true ELSE false END as IsIdentity,CASE WHEN is_nullable = 'YES' THEN true ELSE false END AS `IsNullable`FROM Information_schema.columns where TABLE_NAME='{tableName}' and  TABLE_SCHEMA=(select database()) ORDER BY TABLE_NAME";

SELECT  T1.TABLE_COMMENT 表注釋,T1.TABLE_ROWS 表數據量,T2.TABLE_NAME 表名,T2.COLUMN_NAME 字段名,  T2.COLUMN_TYPE 數據類型,  T2.DATA_TYPE 字段類型,  T2.CHARACTER_MAXIMUM_LENGTH 長度,  T2.IS_NULLABLE 是否為空,  T2.COLUMN_DEFAULT 默認值,  T2.COLUMN_COMMENT 字段備注   
FROM INFORMATION_SCHEMA.TABLES T1
LEFT JOININFORMATION_SCHEMA.COLUMNS T2
ONT1.TABLE_NAME = T2.TABLE_NAME
WHERE  T1.TABLE_SCHEMA ='ftp'
AND T1.TABLE_NAME LIKE 'oemp%'
ORDER BY T1.TABLE_NAME;

二、如何獲取全部表名

基本的語句為

SELECT table_name FROM information_schema.tables

但是這個并不符合業務需求，因為這會返回全部的表名，而業務中需要限定是哪個數據庫，并且，不同的業務可能會使用不同的表前綴，所以最好可以限定表前綴，并且需要展示表的注釋，不然大家也不清楚表是屬于哪個業務的。

所以，完整的SQL語句如下

SELECTTABLE_NAME,TABLE_COMMENT 
FROMinformation_schema.TABLES 
WHERETABLE_SCHEMA = 'TABLE_SCHEMA' AND TABLE_NAME LIKE 'x_%' AND TABLE_NAME NOT LIKE 'xx_exp%' 
ORDER BYTABLE_NAME

需要配置幾個參數，并且已經按表名進行排序，TABLE_COMMENT 為表注釋。

TABLE_SCHEMA 數據庫名稱
x_ 表前綴

運行結果如下圖

1、查看Mysql 數據庫 "ori_data"下所有表的表名、表注釋及其數據量SELECT 
TABLE_NAME 表名,TABLE_COMMENT 表注釋,TABLE_ROWS 數據量
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'ori_data' 
ORDER BY TABLE_NAME;

SELECT* FROM OPENQUERY (MYSQLTEST ,'
SELECTTABLE_NAME as 表名FROMinformation_schema.TABLES 
WHERETABLE_SCHEMA = ''msldbalitest'' AND TABLE_NAME LIKE ''tp_%'' AND TABLE_NAME NOT LIKE ''cms_exp%'' ORDER BY TABLE_NAME desc')

2. 查詢數據庫 ‘ori_data’ 下表 ‘accumulation’ 所有字段注釋SELECT 
COLUMN_NAME 字段名,column_comment 字段注釋 
FROM INFORMATION_SCHEMA.Columns 
WHERE table_name='accumulation' AND table_schema='ori_data'

select COLUMN_NAME,DATA_TYPE,COLUMN_COMMENT from information_schema.COLUMNS where table_name = '表名' and table_schema = '數據庫名稱';

SELECT* FROM OPENQUERY (MYSQLTEST ,'
SELECT 
COLUMN_NAME as 字段名,DATA_TYPE,column_comment as 字段注釋 
FROM INFORMATION_SCHEMA.Columns 
WHERE table_name=''cms_goods'' AND table_schema=''msldbalitest''')

3. 查詢數據庫 "ori_data" 下所有表的表名、表注釋以及對應表字段注釋SELECT 
a.TABLE_NAME 表名,a.TABLE_COMMENT 表注釋,b.COLUMN_NAME 表字段,b.COLUMN_TYPE 字段類型,b.COLUMN_COMMENT 字段注釋
FROM information_schema.TABLES a,INFORMATION_SCHEMA.Columns b 
WHERE b.TABLE_NAME=a.TABLE_NAME AND a.TABLE_SCHEMA='ori_data'

SELECT* FROM OPENQUERY (MYSQLTEST ,'
SELECT 
a.TABLE_NAME as 表名,a.TABLE_COMMENT as 表注釋,b.COLUMN_NAME as 表字段,b.COLUMN_TYPE as 字段類型,b.COLUMN_COMMENT as 字段注釋
FROM information_schema.TABLES a,INFORMATION_SCHEMA.Columns b 
WHERE b.TABLE_NAME=a.TABLE_NAME AND a.TABLE_SCHEMA=''msldbalitest''')

information_schema數據庫是MySQL數據庫自帶的數據庫，里面存放的MySQL數據庫所有的信息，包括數據表、數據注釋、數據表的索引、數據庫的權限等等。

Mysql數據庫如何獲取某數據庫所有表名稱（不包含表結構），Sql如下：

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'xxx' AND table_type = 'base table'

information_schema：Mysql自帶的數據庫，存放各類數據庫相關信息的信息數據庫，表多為視圖
information_schema.tables：該數據庫下的tables表
table_schema：tables表下的一個字段，數據庫名稱
table_type：tables表下的一個字段，表類型，base table為基礎表，注：有空格
table_name：tables表下的一個字段，數據表名稱
?

查看指定表的字段及注釋

SELECT* FROM OPENQUERY (MYSQLTEST ,'
selecta.ordinal_position,a.COLUMN_name,a.COLUMN_type,a.COLumn_comment,a.is_nullable,a.column_key
frominformation_schema.COLUMNS a
whereTABLE_schema = ''msldbalitest''and TABLE_name = ''cms_admin_menu''')

查看數據所有表名及注釋

SELECT* FROM OPENQUERY (MYSQLTEST ,'
selectt.TABLE_NAME,t.TABLE_COMMENT
frominformation_schema.tables t
wheret.TABLE_TYPE = ''BASE TABLE''and TABLE_schema = ''msldbalitest''')

在mysql中，information_schema這個數據庫中保存了mysql服務器所有數據庫的信息。
包括數據庫名，數據庫的表，表字段的數據類型等。
簡而言之，若想知道mysql中有哪些庫，哪些表，表里面有哪些字段以及他們的注釋，都可以從information_schema中獲取
?

COLUMNS表
information_schema庫中的COLUMNS表，存放MySQL所有表的字段詳細信息。

常用列
TABLE_SCHEMA：數據庫名
TABLE_NAME：數據表名
COLUMN_NAME：數據列名
DATA_TYPE：數據類型，如：varchar
COLUMN_TYPE：數據列類型（含數據長度），如：varchar(32)
COLUMN_COMMENT：數據列注釋/說明
?

            string sql = $@"SELECT TABLE_NAME as TableName, column_name AS DbColumnName,CASE WHEN  left(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)-1)='' THEN COLUMN_TYPE ELSE  left(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)-1) END AS DataType,CAST(SUBSTRING(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)+1,LOCATE(')',COLUMN_TYPE)-LOCATE('(',COLUMN_TYPE)-1) AS signed) AS Length,column_default  AS  `DefaultValue`,column_comment  AS  `ColumnComment`,CASE WHEN COLUMN_KEY = 'PRI' THEN true ELSE false END AS `IsPrimaryKey`,CASE WHEN EXTRA='auto_increment' THEN true ELSE false END as IsIdentity,CASE WHEN is_nullable = 'YES' THEN true ELSE false END AS `IsNullable`FROM Information_schema.columns where TABLE_NAME='{tableName}' and  TABLE_SCHEMA=(select database()) ORDER BY TABLE_NAME";

使用MySQL創建的表，無論是表注釋、索引，還是字段的類型等等，都會存到MySQL自帶的庫表中，可以通過SQL查出來想要的表、字段信息。
了解information_schema庫，可以在工作中起到意想不到的效果

-- database_name替換為庫名，查出庫中所有表的TABLE_NAME表名、TABLE_COMMENT表注釋
SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE table_schema='database_name';

TABLES表

information_schema庫中的TABLES表，存放MySQL所有表的表信息。

常用列

TABLE_SCHEMA：數據庫名
TABLE_NAME：數據表名
TABLE_COMMENT：數據表注釋/說明

查詢某個表的所有字段

select column_name,data_type,column_comment,column_key,extra,character_maximum_length,is_nullable,column_default
from information_schema.columns 
where table_schema = 'seata' and table_name = 'users' ;

組裝表的所有列

select GROUP_CONCAT("t.",column_name) total
from information_schema.columns 
where table_schema = 'seata' and table_name = 'users' and column_name not in ('id');