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

user_12715348 0
Viewed 713

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
workbench snowflake-cloud-data-platform sql
2 Answers

I tried running a sub-query for the columns to at least be able to run all columns per table but surprisingly that didn't work.

Used this code to at least be able to get a list of the columns in a specific Schema that might contain the values I'm looking for. It did save me a decent amount of time:

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
user_127153480

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.

user_1324380
Related