search_text_all_tables.sql

DO $$
DECLARE
	v_schema text;
	v_table text;
	v_column text;
	v_look_for text := 'foo';
	v_ret bool;
BEGIN
	FOR v_schema, v_table IN
		SELECT table_schema, table_name
		FROM information_schema.tables
		WHERE table_type = 'BASE TABLE'
			AND table_schema !~ '^(pg_.*|information_schema)$'
	LOOP
		FOR v_column IN
			SELECT column_name
			FROM information_schema.columns
			WHERE table_schema = v_schema
				AND table_name = v_table
	LOOP
		EXECUTE format('SELECT EXISTS(SELECT 1 FROM %I.%I WHERE %I::text LIKE %L)', v_schema, v_table, v_column, v_look_for) INTO v_ret;
		IF (v_ret) THEN
			RAISE NOTICE '%.%.%', v_schema, v_table, v_column;
		END IF;
	END LOOP;
END LOOP;
END;
$$;