查询数据库所有表字段信息

mysql

查询所有表和对应的字段属性

SELECT
    TABLE_SCHEMA AS '库名',
    TABLE_NAME AS '表名',
    COLUMN_NAME AS '列名',
    ORDINAL_POSITION AS '列的排列顺序',
    COLUMN_DEFAULT AS '默认值',
    IS_NULLABLE AS '是否为空',
    DATA_TYPE AS '数据类型',
    CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
    NUMERIC_PRECISION AS '数值精度(最大位数)',
    NUMERIC_SCALE AS '小数精度',
    COLUMN_TYPE AS 列类型,
    COLUMN_KEY 'KEY',
    EXTRA AS '额外说明',
    COLUMN_COMMENT AS '注释'
FROM
    information_schema.`COLUMNS`
WHERE
	-- aaa为库名
    TABLE_SCHEMA = 'aaa'
ORDER BY
    TABLE_NAME,
    ORDINAL_POSITION;

mysql查询所有数据库表名和表注释

SELECT table_name, table_comment 
FROM information_schema.tables 
WHERE table_schema = '数据库名';

oracl

来源：https://www.cnblogs.com/xusir/p/3214714.html
获取表

select table_name from user_tables; //当前用户拥有的表      
 
select table_name from all_tables; //所有用户的表 
select table_name from dba_tables; //包括系统表
select table_name from dba_tables where owner='用户名'
 
-- user_tables：
-- table_name,tablespace_name,last_analyzed等
-- dba_tables：
-- ower,table_name,tablespace_name,last_analyzed等
-- all_tables：
-- ower,table_name,tablespace_name,last_analyzed等
-- all_objects：
-- ower,object_name,subobject_name,object_id,created,last_ddl_time,timestamp,status等

获取表字段

select * from user_tab_columns where Table_Name='用户表';
select * from all_tab_columns where Table_Name='用户表';
select * from dba_tab_columns where Table_Name='用户表';
-- user_tab_columns：
-- table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等
-- all_tab_columns ：
-- ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等
-- dba_tab_columns：
-- ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等

获取表注释

select * from user_tab_comments
-- user_tab_comments：table_name,table_type,comments
-- 相应的还有dba_tab_comments，all_tab_comments，这两个比user_tab_comments多了ower列。
-- 获取字段注释：
select * from user_col_comments
-- user_col_comments：table_name,column_name,comments
-- 相应的还有dba_col_comments，all_col_comments，这两个比user_col_comments多了ower列。