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

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

Thanks @Jim_Knicely
Also let me know How to find out all the tables with no data.
Tables can be in various schemas for eg-A,B,C are tables. X and Y are schemas
A& B doesn't have any data and A is present in X schema and B is present in Y schema.

Please let me know the query for this or any other way.

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