sql - 从表有 > 0 行的 all_tab_columns 中选择

Question

我需要在大型数据库中搜索与列名匹配的表，但该表必须超过 0 行。

顺便问一下:

SELECT * FROM all_tab_columns WHERE column_name LIKE '%ID_SUPPORT%';

Answer 1

您可以使用单个查询来过滤名称并获取实际行数:

SELECT owner, table_name, cnt              
FROM all_tab_columns, XMLTABLE('/ROWSET/ROW' passing 
             (dbms_xmlgen.getxmltype(REPLACE(REPLACE(
             'select COUNT(*) AS cnt from <owner>.<table_name>', '<owner>', owner)
             , '<table_name>', table_name))) COLUMNS cnt INT)
WHERE column_name LIKE '%ID_SUPPORT%' AND cnt > 0;

DBFiddle Demo

---

Any chance this can be expanded/tweaked to yield the values of the first few rows for all tables?

是的，通过使用 JSON_ARRAYAGG(JSON_OBJECT(*)) Oracle 19c 展平行:

-- generic approach Oracle 19c
SELECT owner, table_name, cnt, example             
FROM all_tab_columns, XMLTABLE('/ROWSET/ROW' passing 
                 (dbms_xmlgen.getxmltype(REPLACE(REPLACE(
                  'select COUNT(*) AS cnt,
                          MAX((SELECT JSON_ARRAYAGG(JSON_OBJECT(*)) 
                               FROM <owner>.<table_name> 
                               WHERE rownum < 10)  -- taking up to 10 rows as example
                              ) as example 
                          from <owner>.<table_name>', '<owner>', owner)
                 , '<table_name>', table_name))) 
                 COLUMNS cnt INT
                        , example VARCHAR2(1000))
WHERE column_name LIKE '%ID_SUPPORT%'
      AND cnt > 0;

演示包含 JSON_OBJECT 中的硬编码列列表。 Oracle 19c 和 JSON_OBJECT(*) 将允许每个表有任何列列表。

db<>fiddle demo

工作原理:

1. 查找列名为 '%ID_SUPPORT' 的所有表
2. 使用 dbms_xml_gen.getxmltype 对每个表运行查询
3. 在子查询中计算行数，将几行扁平化为 JSON 的示例
4. 返回至少有一个记录表的行