Find a value anywhere in a Vertica database

Question

Is there a way to search all columns of all tables in a given database for a specific value (numeric or string) in Vertica?

This question is inspired by Find a value anywhere in a database for SQL Server.

Answer 1

Careful what you wish for.

This one took 13 minutes to run only in my small database, in the public schema.

CREATE OR REPLACE PROCEDURE public.findstring (
  IN searcharg   VARCHAR -- search argument
, IN _tbschema   VARCHAR -- schema name - empty string if all schemas
) LANGUAGE PLvSQL SECURITY INVOKER AS $$
DECLARE
  tbschema VARCHAR(128);
  tbname   VARCHAR(128);
  colname  VARCHAR(128);
  sq       VARCHAR(65000);
  rowsfound INT;
BEGIN
  IF _tbschema = '' THEN
     _tbschema = '%';
  END IF; 
  FOR tbschema, tbname, colname IN QUERY 
    SELECT c.table_schema,c.table_name,column_name FROM columns c JOIN tables t USING(table_id)
    WHERE data_type_id < 17 -- avoid non-castable data types
      AND NOT c.is_system_table
      AND table_definition = '' -- not an external table
      AND c.table_schema ~~* _tbschema
    ORDER BY 1,2,ordinal_position
  LOOP
    sq:='SELECT COUNT(*) as rowsfound FROM '||QUOTE_IDENT(tbschema)||'.'||QUOTE_IDENT(tbname)||
       ' WHERE '||QUOTE_IDENT(colname)||'::VARCHAR='||QUOTE_LITERAL(searcharg);
    rowsfound := EXECUTE sq; 
    IF rowsfound > 0 THEN
      RAISE NOTICE 'found % instances in %.%.%',rowsfound,tbschema,tbname,colname;
    END IF; 
   END LOOP;
END;
$$;

CALL findstring('42','public');                                                                                                                                                                                

01000:1:2005:NOTICE 2005:  found 1 instances in public.at1.id
01000:2:2005:NOTICE 2005:  found 79 instances in public.ate.id
01000:3:2005:NOTICE 2005:  found 8600225 instances in public.ate.ix
01000:4:2005:NOTICE 2005:  found 791 instances in public.ate.ae
01000:5:2005:NOTICE 2005:  found 79 instances in public.ati.id
01000:6:2005:NOTICE 2005:  found 8600225 instances in public.ati.ix
01000:7:2005:NOTICE 2005:  found 791 instances in public.ati.ae
01000:8:2005:NOTICE 2005:  found 158 instances in public.att.id
01000:9:2005:NOTICE 2005:  found 52479 instances in public.att.ix
01000:10:2005:NOTICE 2005:  found 3 instances in public.att.ae
01000:11:2005:NOTICE 2005:  found 5 instances in public.prod_dim.product_key
01000:12:2005:NOTICE 2005:  found 597 instances in public.prod_dim.weight
01000:13:2005:NOTICE 2005:  found 19 instances in public.prod_dim.product_price
01000:14:2005:NOTICE 2005:  found 193 instances in public.prod_dim.product_cost
01000:15:2005:NOTICE 2005:  found 22 instances in public.prod_dim.lowest_competitor_price
01000:16:2005:NOTICE 2005:  found 16 instances in public.prod_dim.highest_competitor_price
01000:17:2005:NOTICE 2005:  found 28 instances in public.prod_dim.average_competitor_price
01000:18:2005:NOTICE 2005:  found 5 instances in public.product_dimension.product_key
01000:19:2005:NOTICE 2005:  found 597 instances in public.product_dimension.weight
01000:20:2005:NOTICE 2005:  found 19 instances in public.product_dimension.product_price
01000:21:2005:NOTICE 2005:  found 193 instances in public.product_dimension.product_cost
01000:22:2005:NOTICE 2005:  found 22 instances in public.product_dimension.lowest_competitor_price
01000:23:2005:NOTICE 2005:  found 16 instances in public.product_dimension.highest_competitor_price
01000:24:2005:NOTICE 2005:  found 28 instances in public.product_dimension.average_competitor_price
01000:25:2005:NOTICE 2005:  found 1 instances in public.ttest.c_int
findstring
         0
call succeeded; 1 row fetched
Timing:|_tm_prepare  |_tm_execute  |_tm_cmd_total|_tm_fetch_1st|_tm_fetch_tot                                                                                                                                  
Timing:| 00:00:00.016| 00:13:11.408| 00:13:11.425| 00:00:00.000| 00:00:00.000