SQL Find All Tables and Fields Where a Specific Value is Present

Question

I'm not an expert on SQL so I'm having a little bit of a hard time trying to implement or modify what I've seen online for my case.

Trying to build a query from a Schema I'm not familiar with. The Schema has 50+ tables each with varying amounts of columns.

I have a couple of values but can't find the field or table those values are in.

Was trying to find these values with something like this (see code below) which would help but I can't figure out a way to:

1. Include all columns without having to type them all out inside the IN operator.
2. Not sure if there is a way to modify the code so I wouldn't have to run it for each table in the Schema.

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

LIMIT
10000

Answer 1

This SQL script is not going to be fast, but it works:

• Find all string columns in tables in this database.
• For each string column found above, we are going to insert the # of rows that match the condition.

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

This would work faster if we would parallelize each column scan.