Need to retrieve particular string from all tables and get table names

Best Answers

• Options

  Jim_Knicely - Select Field - Administrator

  December 2020 edited December 2020 Answer ✓

  Hi,

  Here's a Vertica Quick Tip that might help you out here:

  https://www.vertica.com/blog/finding-all-columns-storing-some-value-quick-tip/

  And here is a slightly modified example to find any table that has any VARCHAR columns which contain only 10 digits:

  dbadmin=> CREATE TABLE phone (c VARCHAR(10));
  CREATE TABLE
  
  dbadmin=> INSERT INTO phone SELECT '0123456789';
   OUTPUT
  --------
        1
  (1 row)
  
  dbadmin=> COMMIT;
  COMMIT
  
  dbadmin=> \! vsql -Atc "SELECT 'SELECT ''' || table_schema || '.' || table_name || '.' || column_name || ''' FROM dual WHERE EXISTS (SELECT NULL FROM ' || table_schema || '.' || table_name || ' WHERE regexp_like(' || column_name || ', ''^\d{10}$''));' FROM columns WHERE NOT is_system_table AND data_type_id = 9 ORDER BY table_schema, table_name, ordinal_position;" | vsql -At
  public.phone.c
  

  1
• Options

  mosheg Vertica Employee Administrator

  December 2020 Answer ✓

  See: https://forum.vertica.com/discussion/242005/how-to-find-out-all-the-tables-with-no-data#latest

  0