#查詢(xún)所有的數(shù)據(jù)庫(kù)名稱(chēng)
SELECT SCHEMA_NAME AS Database FROM INFORMATION_SCHEMA.SCHEMATA;

#查詢(xún)指定數(shù)據(jù)庫(kù)下的所有表名（例如information_schema數(shù)據(jù)庫(kù)下的所有表名）

select table_name as name  from information_schema.TABLES where TABLE_SCHEMA='information_schema'

            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 表數(shù)據(jù)量,
    T2.TABLE_NAME 表名,
    T2.COLUMN_NAME 字段名,  
    T2.COLUMN_TYPE 數(shù)據(jù)類(lèi)型,  
    T2.DATA_TYPE 字段類(lèi)型,  
    T2.CHARACTER_MAXIMUM_LENGTH 長(zhǎng)度,  
    T2.IS_NULLABLE 是否為空,  
    T2.COLUMN_DEFAULT 默認(rèn)值,  
    T2.COLUMN_COMMENT 字段備注   
FROM 
    INFORMATION_SCHEMA.TABLES T1
LEFT JOIN
    INFORMATION_SCHEMA.COLUMNS T2
ON
    T1.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

SELECT
	TABLE_NAME,
	TABLE_COMMENT 
FROM
	information_schema.TABLES 
WHERE
	TABLE_SCHEMA = 'TABLE_SCHEMA' 
	AND TABLE_NAME LIKE 'x_%' 
	AND TABLE_NAME NOT LIKE 'xx_exp%' 
ORDER BY
	TABLE_NAME

1、查看Mysql 數(shù)據(jù)庫(kù) "ori_data"下所有表的表名、表注釋及其數(shù)據(jù)量

SELECT 
TABLE_NAME 表名,TABLE_COMMENT 表注釋,TABLE_ROWS 數(shù)據(jù)量
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'ori_data' 
ORDER BY TABLE_NAME;

SELECT* FROM OPENQUERY (MYSQLTEST ,'
SELECT
	TABLE_NAME as 表名
	 
FROM
	information_schema.TABLES 
WHERE
	TABLE_SCHEMA = ''msldbalitest'' 
	AND TABLE_NAME LIKE ''tp_%'' 
	AND TABLE_NAME NOT LIKE ''cms_exp%'' 
    ORDER BY TABLE_NAME desc

')

2. 查詢(xún)數(shù)據(jù)庫(kù) ‘ori_data' 下表 ‘a(chǎn)ccumulation' 所有字段注釋

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 = '數(shù)據(jù)庫(kù)名稱(chēng)';

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. 查詢(xún)數(shù)據(jù)庫(kù) "ori_data" 下所有表的表名、表注釋以及對(duì)應(yīng)表字段注釋

SELECT 
a.TABLE_NAME 表名,a.TABLE_COMMENT 表注釋,b.COLUMN_NAME 表字段,b.COLUMN_TYPE 字段類(lèi)型,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 字段類(lèi)型,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''

')

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

SELECT* FROM OPENQUERY (MYSQLTEST ,'
select
	a.ordinal_position,
	a.COLUMN_name,
	a.COLUMN_type,
	a.COLumn_comment,
	a.is_nullable,
	a.column_key
from
	information_schema.COLUMNS a
where
	TABLE_schema = ''msldbalitest''
	and TABLE_name = ''cms_admin_menu''

')

SELECT* FROM OPENQUERY (MYSQLTEST ,'
select
	t.TABLE_NAME,
	t.TABLE_COMMENT
from
	information_schema.tables t
where
	t.TABLE_TYPE = ''BASE TABLE''
	and TABLE_schema = ''msldbalitest''
')

            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";

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

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');