DECLARE
  ncount NUMBER;
  vwhere VARCHAR2(1000) := '';
  vselect VARCHAR2(1000) := ' select count(1) from "';
  vsearchstr VARCHAR2(1000) := 'IDX';
BEGIN
  FOR k IN (SELECT a.table_name
                  ,a.column_name
              FROM user_tab_cols a
             WHERE a.data_type LIKE '%VARCHAR%')
  LOOP
    vwhere := '" where "' || k.column_name || '" = :vsearchstr ';
    EXECUTE IMMEDIATE vselect || k.table_name || vwhere
      INTO ncount
      USING vsearchstr;
    IF (ncount > 0)
    THEN
      dbms_output.put_line(k.column_name || ' ' || k.table_name);
    END IF;
  END LOOP;
END;