某个查询模式搜索整个数据库

Question

我需要在整个模式中找到一个以例如ABC开头的表模式(Oracle DB(，我可以用更好的示例来解释

ABC-例如指阶段模式ABC_CUST_ORDER_INFO

但我目前面临的问题是，在数据库中，我有很多模式和表，大多数表有时以ABC、BBC和XYZ等开头。。。这是非常令人困惑的，所以

查询-如果一个查询足够灵活，可以递归地获取模式名称，则模式计数

模式-从类似通配符搜索开始

STAGE      PATTERN COUNT
DWH_STAGE  ABC    1000
DWH_STAGE  NBC     200
DWH_STAGE  XYZ     150
DWH_EDW    HSS     2001
DWH_EDW    DGS     300

2. 我还需要一个单独的查询-我在同一模式中有不同的表，比如以_D、_F，_T和_W或任何这样的模式结尾，这样我就可以灵活地使用

示例-表

ABC_CUST_ORDER_INFO_D
ABC_CUST_ADD_INFO_F
ABC_SHPNG_STTS_V
BBC_PRDCT_CRNT_VALUE_W
BBC_PRDCT_CRNT_SALE_T
CNN_PAYMT_INFO_F

因此，如果一个查询没有显示以上述(_D、_F、_T、_W、_V或任何这样的模式(结尾的表

你能提出建议吗？

我尝试过使用多种方法

选项1(使用通配符搜索

select * from <tbl> where col_name like 'ABC%'
where schema='schema_name'
union
select * from <tbl> where col_name like 'BBC%'
where schema='schema_name'

但我至少有100多个像ABC_、BBC_、XZY_这样的开头，似乎这个选项会使复杂化

选项2(使用REGEXP_SUBSTR

SELECT
colname,
REGEXP_SUBSTR(col3, '%col3{(.*?)}', 1, 1, NULL, 1) AS colname
FROM tblname; 

我试过了它不起作用

例如，让我们考虑

表-CUST_ORDER_INFO

COL 1 - SCHEMA_NAME - DWH_STAGE (record)
COL 2 - CUST_ORDER_INFO (record)
COL 3 - CUST_INFO - ABC_CUST_ORDER_INFO (record)

示例记录-ABC_CUST_ORDER_INFO、BBC_PRDCT_CRNT_VALUE_W

所以预期的结果就像这个

SCHEMA_NAME CUST_ORDER_INFO CUST_INFO               PATTERN  COUNT
DWH_STAGE   SHOES           ABC_CUST_ORDER_INFO     ABC      1000
DWH_STAGE   SHOES           BBC_PRDCT_CRNT_VALUE_W  BBC      300

Answer 1

此查询将在数据库中查找以XXX_开头的表并按前缀和模式将它们分组：

SELECT owner AS schema_name, SUBSTR (table_name, 1, 3) AS beginning, COUNT (*) AS COUNT
FROM dba_tables
WHERE REGEXP_LIKE (table_name, '^[[:alpha:]]{3}_')
GROUP BY owner, SUBSTR (table_name, 1, 3)
ORDER BY 1, 2;

此查询将执行相同的操作，但按结尾的表的最后一个字符进行分组_X

SELECT owner AS schema_name, SUBSTR (table_name, -1) AS ending, COUNT (*) AS COUNT
FROM dba_tables
WHERE REGEXP_LIKE (table_name, '_[[:alpha:]]$')
GROUP BY owner, SUBSTR (table_name, -1)
ORDER BY 1, 2

如果愿意，您可以修改任一查询，只查看特定模式中的表。