查找特定值存在的所有表和字段的SQL查询

Question

我不是SQL专家，所以在尝试在线上看到的内容进行实现或修改时遇到了些困难。

我试图从一个我不熟悉的架构中构建一个查询。该架构有50多个表，每个表都有不同数量的列。

我有一些值，但找不到这些值所在的字段或表。

本来想用以下代码查找这些值，但我想不出一种方法:

1. 包含所有列而无需在IN操作符中逐个键入它们。
2. 不确定是否有一种方法可以修改代码，使我不必为架构中的每个表运行它。

SELECT
*
FROM
    XX_PROD.XXX_MART.FACT_SALES_ORDER AS FSO
    
WHERE 'AVP' IN (FSO.COLUMNS)

LIMIT
10000

- superavd88

---

我认为没有简单的方法可以实现这一点，你可能需要构建一个自定义存储过程来完成它。SQL并没有被定义为以这种方式工作。 - Eric Lin

Answer 1

这个SQL脚本可能不太快，但是它可以运行：

• 查找此数据库中所有表中的字符串列。
• 针对上述每个字符串列，我们将插入与条件匹配的行数。

declare
    table_name string;
    column_name string;
    all_columns cursor for (
        -- find all string columns in tables in this database
        -- add restrictions here for less scans later
        select table_schema, table_name, column_name
        from information_schema.columns
        where table_schema != 'INFORMATION_SCHEMA'
        and data_type = 'TEXT'
    );
begin
    -- create a table to store results
    create or replace temp table discovery_results(table_name string, column_name string, matches int);
    for record in all_columns do
        table_name := record.table_schema || '.' || record.table_name;
        column_name := record.column_name;

        -- for each string column found above, we are going to insert the # of rows that match the condition
        insert into discovery_results  
            select :table_name, :column_name, count(*) 
            from identifier(:table_name)
            -- change the condition you are looking for here
            where identifier(:column_name) like '%string%'
        ;
    end for;
    return 'run [select * from discovery_results] to find the results';
end;


select *
from discovery_results

如果我们并行化每个列扫描，这将会更快。

- Felipe Hoffa

Answer 2

我尝试运行一个子查询，至少能够对每个表运行所有列，但出乎意料的是，这并没有起作用。

为了至少能够获取包含我正在寻找的值的特定模式中列的列表，我使用了这段代码。它确实节省了我相当多的时间：

SELECT TABLE_NAME,COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE 
   TABLE_SCHEMA like 'XXXX_MART'
   AND COLUMN_NAME like '%START_DATE%'
   
ORDER BY
TABLE_NAME ASC

- superavd88