Oracle和MySQL查询所有的表信息和字段信息

1. MySQL

1.1 查询表

• 如下：

  SELECT
  table_comment 表中文名,
  table_name 表英文名
  FROM information_schema.TABLES
  WHERE table_schema = 'test2022'
  ORDER BY table_name;
  

  

1.2 查询字段

1.2.1 方式1->SHOW FULL COLUMNS

• 如下：

  SHOW FULL COLUMNS FROM `sys_login_log`;
  

  

1.2.2 方式2->information_schema.COLUMNS

• 如下：

  select * from information_schema.COLUMNS b
  where 1=1
  and b.TABLE_SCHEMA='test2022'
  and b.table_name='sys_login_log';
  

  

1.3 查表和字段

• 如下：

  SELECT
  a.table_comment 表中文名称,
  a.table_name 表英文名称,
  b.COLUMN_NAME 字段英文名,
  b.column_comment 字段中文名,
  b.column_type 字段类型,
  b.column_key 主键约束
  FROM
  information_schema. TABLES a
  LEFT JOIN information_schema. COLUMNS b ON a.table_name = b.TABLE_NAME
  WHERE 1=1
  and a.table_schema = 'test2022'
  AND a.table_name = 'sys_login_log';
  ORDER BY
  a.table_name;
  

  

1.4 查表和字段–>转程Oracle需要的数据类型

• 如下：

  SELECT
  a.table_comment 表中文名称,
  a.table_name 表英文名称,
  b.COLUMN_NAME 字段英文名,
  b.column_comment 字段中文名,
  -- b.column_type 字段类型,
  (case b.column_type when 'bigint(20) unsigned' then 'NUMBER(20)' 
  when 'tinyint(1)' then 'NUMBER(1)'
  when 'tinyint(4)' then 'NUMBER(4)'
  when 'bigint(20)' then 'NUMBER(20)'
  when 'int(11)' then 'NUMBER(11)'
  WHEN 'json' THEN 'CLOB'
  WHEN 'text' THEN 'CLOB'
  when 'datetime' then 'DATE'
  else b.column_type end) 字段类型,
  -- b.column_key 主键约束
  (case b.column_key when 'PRI' then '是' else b.column_key end) 是否主键
  FROM
  information_schema. TABLES a
  LEFT JOIN information_schema. COLUMNS b ON a.table_name = b.TABLE_NAME
  WHERE 1=1
  and a.table_schema = 'test2022'
  AND a.table_name = 'sys_login_log';
  ORDER BY
  a.table_name;
  

  

2. Oracle

2.1 查表和字段的单表查询

• 直接看下面sql ，不截图了，自己下去查查看

  --1.所有用户的表
  select * from all_tables; 
  
  --2.包括系统表
  select * from dba_tables; 
  
  --3.查询所有的表
  select t.* from user_tables t;
  
  --4.查询当前用户下所有的表带注释
  select * from user_tab_comments t where t.TABLE_TYPE='TABLE';
  
  --5.查询所有的字段(表名、字段、字段注释)
  select t.* from user_col_comments t;
  
  --6.根据表名查询所有的字段（带字段名、字段类型、字段长度）
  select * from all_tab_columns t where t.table_name='AC_USER';
  

2.2 整理查表和字段的sql

• 如下：

  select t1.table_name,t1.comments as table_comments, 
  t2.column_name,t2.comments as column_comments,
  t3.data_type,t3.data_length,t3.column_id
  from user_tab_comments t1 
  left join user_col_comments t2 on t2.table_name = t1.table_name
  left join all_tab_columns t3 on(t3.table_name=t1.table_name and t3.column_name=t2.column_name)
  where t1.TABLE_TYPE='TABLE'
  and t1.table_name='SYS_COMPANY_DEPT';