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

SK21

Please help me in building a query to retrieve table names from db
which contains 10 digit phone numbers in any column.
Numbers are in numeric format an could be of 12 digit also but most of them are have 10 numeric digits only.

Jim_Knicely

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

mosheg

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

SK21

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.