mysql查询表名是否存在和oracle查询表名是否存在

一、Oracle

oracle中查询表名是否存在

select table_name from user_tables where table_name='B_IN_DETAIL';

 

二、mysql

1、mysql中查询表名是否存在

select table_name from information_schema.`TABLES` where table_name = 'b_in'

 2、mysql查询数据库所有的表名

show tables from 数据库名称

3、查询mysql数据库的所有表名的中文注释

SELECT TABLE_NAME, TABLE_COMMENT 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'xxx';

4、查询mysql数据库的所有表名和记录数

SELECT 
        TABLE_NAME AS 'TableName',
    TABLE_NAME AS 'TableChineseName',
    TABLE_ROWS AS 'Records'
FROM 
    INFORMATION_SCHEMA.TABLES
WHERE 
    TABLE_SCHEMA = 'xxx';

5、查询mysql数据库的所有表名和数据长度

SELECT 
    table_name AS 'Table',
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Data Length (MB)'
FROM 
    information_schema.TABLES 
WHERE 
    TABLE_SCHEMA = 'xxxx';

6、查询mysql数据库的表的字段名称

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'mtyh' AND TABLE_NAME = 't_area_code';

7、查询mysql数据库的表的字段名称和注释

SELECT 
    COLUMN_NAME as 'Field', 
    COLUMN_COMMENT as 'Comment'
FROM 
    information_schema.columns 
WHERE 
    table_schema = 'mtyh' 
    AND table_name = 't_area_code';

8、查询mysql数据库的表的字段名称和注释、字段类型、是否主键

SELECT 
    COLUMN_NAME AS 'Field',
        COLUMN_COMMENT AS 'Comment',
    COLUMN_TYPE AS 'Type',
    (CASE WHEN COLUMN_KEY = 'PRI' THEN '是' ELSE '否' END) AS 'Key'
FROM 
    INFORMATION_SCHEMA.COLUMNS 
WHERE 
    TABLE_SCHEMA = 'mtyh' 
    AND TABLE_NAME = 't_area_code';

9、查询mysql数据库的表的字段名称和注释、字段类型、是否主键、是否为null、默认值

SELECT 
    COLUMN_NAME AS 'Field',
        COLUMN_COMMENT AS 'Comment',
    COLUMN_TYPE AS 'Type',
    (CASE WHEN COLUMN_KEY = 'PRI' THEN '是' ELSE '否' END) AS 'Key',
        (CASE WHEN IS_NULLABLE = 'YES' THEN '是' ELSE '否' END) as '是否允许为NULL',
        COLUMN_DEFAULT as '默认值'
FROM 
    INFORMATION_SCHEMA.COLUMNS 
WHERE 
    TABLE_SCHEMA = 'mtyh' 
    AND TABLE_NAME = 't_area_code';