Oracle数据库元数据查询(数据库、表结构、存储过程）

-- 获取所有数据库名称
select db_unique_name from v$Database
select distinct owner from all_tables order by owner

-- 查表的字段信息
SELECT * FROM COLS WHERE TABLE_NAME='tb_AR' 
SELECT * FROM ALL_TAB_COLS WHERE OWNER='CSFRAMEWORKV6_NORMAL' AND TABLE_NAME='tb_AR';

--查数据库所有用户表
SELECT * FROM user_tables ORDER BY table_name
SELECT* FROM ALL_TABLES WHERE OWNER='CSFRAMEWORKV6_NORMAL'

--查数据库所有表的注释
SELECT * FROM USER_TAB_COMMENTS;
SELECT * FROM ALL_TAB_COMMENTS  WHERE OWNER='CSFRAMEWORKV6_NORMAL' AND TABLE_NAME='tb_AR';

--查存储过程参数
SELECT * FROM ALL_ARGUMENTS WHERE OBJECT_NAME = 'USP_TEST'

-- 查表的主键\外键\索引信息
SELECT * FROM all_constraints WHERE TABLE_NAME='tb_AR' AND constraint_type = 'P'
SELECT * FROM all_cons_columns  WHERE TABLE_NAME='tb_AR' 

--查表结构
SELECT t1.Table_Name AS "表名称",      t3.Comments AS "表说明",    t1.column_id AS "顺序",   t1.Column_Name AS "字段名称",    t1.Data_Type AS "数据类型",    t1.Data_Length AS "长度",    t1.DATA_PRECISION AS "精度",
t1.DATA_SCALE AS "小数位",   t1.Nullable AS "是否为空",    t2.Comments AS "字段说明",    t1.Data_Default AS "默认值" ,   CASE WHEN tt.column_name IS NULL THEN '' ELSE 'Y' END AS "主键" 
FROM COLS t1 
LEFT JOIN USER_COL_COMMENTS t2 on t1.Table_name=t2.Table_name and t1.Column_Name=t2.Column_Name 
LEFT JOIN USER_TAB_COMMENTS t3 on t1.Table_name=t3.Table_name
LEFT JOIN (SELECT cols.table_name, cols.column_name FROM all_constraints cons, all_cons_columns cols WHERE cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner AND cols.table_name = 'tb_AR') tt
on  t1.Table_name=tt.Table_name and t1.Column_Name=tt.Column_Name 
WHERE t1.table_name='tb_AR' AND NOT EXISTS ( SELECT t4.Object_Name FROM User_objects t4 WHERE t4.Object_Type='TABLE' AND t4.Temporary='Y' AND t4.Object_Name=t1.Table_Name ) 
ORDER BY t1.Table_Name, t1.Column_ID 

SELECT    t1.owner AS "owner",      t1.Table_Name AS "表名称",      t1.column_id AS "顺序",   t1.Column_Name AS "字段名称",    t1.Data_Type AS "数据类型",    
t1.Data_Length AS "长度",    t1.DATA_PRECISION AS "精度",   t1.DATA_SCALE AS "小数位",   t1.Nullable AS "是否为空",    t1.Data_Default "默认值",    
t2.Comments AS "字段说明",    t3.Comments AS "表说明",    CASE WHEN tt.column_name IS NULL THEN '' ELSE 'Y' END AS "主键" 
FROM ALL_TAB_COLS t1 
LEFT JOIN ALL_COL_COMMENTS t2 on t1.owner=t2.owner AND t1.Table_name=t2.Table_name and t1.Column_Name=t2.Column_Name 
LEFT JOIN ALL_TAB_COMMENTS t3 on t1.owner=t3.owner AND t1.Table_name=t3.Table_name 
LEFT JOIN (SELECT cols.owner, cols.table_name, cols.column_name FROM ALL_CONSTRAINTS cons, ALL_CONS_COLUMNS cols WHERE cons.owner = cols.owner AND cons.table_name = cols.table_name AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cols.table_name = '_Demo_Customer') tt on t1.owner=tt.owner and t1.Table_name=tt.Table_name and t1.Column_Name=tt.Column_Name 
WHERE t1.owner='CSFRAMEWORKV6_NORMAL' AND t1.table_name='_Demo_Customer' 
AND t2.owner='CSFRAMEWORKV6_NORMAL'  AND t2.table_name='_Demo_Customer' 
AND NOT EXISTS ( SELECT t4.Object_Name FROM USER_OBJECTS t4 WHERE t4.Object_Type='TABLE' AND t4.Temporary='Y' AND t4.Object_Name=t1.Table_Name ) 
ORDER BY t1.Table_Name, t1.Column_ID