sql - 加入 oracle 元数据表
问题描述
我正在尝试加入元数据表以查询表上的统计信息,例如触发器计数、序列、包、过程、视图等,但我找不到加入表的键。这是我的查询
SELECT *
FROM user_constraints a
JOIN user_tables b ON a.table_name = b.table_name
JOIN user_triggers c ON a.table_name = c.table_name;
我想做一个大查询来拉回所有主要元数据表中的所有数据谢谢
解决方案
尝试这个:
-- This SQL will show all dependent objects for a table including FK references and index references
-- =================================================================================================
-- constraint references
select to_char(sysdate, 'DD-MON-YYYY') date_evaluated
, cons.owner ||'.'|| cons.table_name || ' (TABLE)' as object
, 'FK Reference to' relationship
, col.owner ||'.'|| col.table_name || ' (TABLE)' as referenced_object
, cons.owner owner
, cons.table_name name
, 'TABLE' type
, col.owner referenced_owner
, col.table_name referenced_name
, 'TABLE' referenced_type
from dba_cons_columns col
, dba_constraints cons
where 1=1
and cons.owner = nvl(:object_owner, cons.owner)
and cons.table_name = nvl(:object_name, cons.table_name)
and cons.r_owner = col.owner
and cons.r_constraint_name = col.constraint_name
union
-- object references from dba_dependencies
select /*+ MATERIALIZE */ to_char(sysdate, 'DD-MON-YYYY') date_evaluated
, referenced_owner || '.' || referenced_name || '(' || referenced_type || ')' as object
, 'Referenced in' relationship
, owner || '.' || name || '(' || type || ')' as referenced_object
, referenced_owner
, referenced_name
, referenced_type
, owner
, name
, type
from dba_dependencies
where 1=1
and name not like 'BIN$%'
and referenced_name not like 'BIN$%'
and type in ('TABLE', 'MATERIALIZED VIEW', 'VIEW', 'PACKAGE', 'TRIGGER', 'INDEX')
and referenced_type in ('TABLE', 'MATERIALIZED VIEW', 'VIEW')
and referenced_owner like nvl(:object_owner, referenced_owner)
and referenced_name like nvl(:object_name, referenced_name)
union
-- object references from dba_indexes
select /*+ MATERIALIZE */ to_char(sysdate, 'DD-MON-YYYY') date_evaluated
, table_owner || '.' || table_name || '(TABLE)' as object
, 'Referenced in' relationship
, owner || '.' || index_name || '(INDEX)' as referenced_object
, table_owner referenced_owner
, table_name referenced_name
, '(TABLE)' referenced_type
, owner owner
, index_name name
, '(INDEX)' type
from dba_indexes
where 1=1
and table_owner like nvl(:object_owner, table_owner)
and table_name like nvl(:object_name, table_name)
;
推荐阅读
- excel - For循环在VBA中找不到价值
- sql-server - 作业和 sp_MSForEachTable 中的 SET QUOTED_IDENTIFIER 错误
- ios - 为什么我的 Swift iOS 构建会出错:“在范围内找不到‘睡眠’”
- javascript - 类访问的多个父 div 的子 div 不会被 .hide() 隐藏
- google-apps-script - 我设置了一个 Google Apps 脚本来自动归档旧电子邮件。它曾经可以工作,但现在我收到授权错误
- regex - 多天读取多个通配符文件模式 - pyspark
- python - 如何单击硒中的按钮?(Python)
- javascript - Amcharts Multi axys 仅转换为 x,y
- excel - 使用变量选择工作表
- typescript - 打字稿:在编译时收获类、属性和方法的详细信息