Search all tables inside of PostgreSQL up to a certain size. This is good for finding those smaller tables when a sample of data is given to you.

PostgreSQL_needle_in_a_haystack.sql

DO
$$
DECLARE
  value int := 0;
  sql text := 'The constructed select statement';
  rec1 record;
  rec2 record;
BEGIN
  FOR rec1 IN
        SELECT
            columns.table_schema
        ,   columns.table_name
        ,   columns.column_name
        -- ,   pg_size_pretty(pg_total_relation_size(table_schema||'.'||table_name))
        FROM       information_schema.columns
        INNER JOIN information_schema.tables  USING (table_catalog,table_schema,table_name)
        WHERE 1=1
          AND columns.table_name <> '_x'
          AND UPPER(columns.column_name) LIKE UPPER('%%')
          AND columns.table_schema <> 'pg_catalog'
          AND columns.table_schema <> 'information_schema'
          AND columns.data_type IN ('character varying', 'text', 'character', 'char', 'varchar')
          AND pg_total_relation_size(columns.table_schema||'.'||columns.table_name) < 5000000 -- size in bytes
          AND columns.table_schema LIKE 'public'
          AND tables.table_type = 'BASE TABLE'
          AND columns.column_name LIKE '%desc%'
        LOOP
    sql := concat('SELECT ', rec1."column_name", ' AS "found" FROM ',rec1."table_schema" , '.',rec1."table_name" , ' WHERE UPPER(',rec1."column_name" , ') LIKE UPPER(''','%needle search term goes here%' , ''') LIMIT 1');
    BEGIN
        FOR rec2 IN EXECUTE sql LOOP
            RAISE NOTICE '%', sql; -- if this fires, look in the output window and get that query. 
            --INSERT INTO _x VALUES (rec1."table_schema", rec1."table_name", rec1."column_name", rec2."found");
        END LOOP;
    EXCEPTION WHEN OTHERS THEN
    END;
  END LOOP;
  END;
$$;