How to find out all the tables with no data.

SK21

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.

mosheg

with num_rows as (
    select sc.schema_name,
           p.anchor_table_name as table_name,
           sum(sc.total_row_count - sc.deleted_row_count) as rows
    from v_monitor.storage_containers sc
    join v_catalog.projections p
         on sc.projection_id = p.projection_id
         and p.is_super_projection = true
    group by schema_name,
             table_name
) 
select t.table_schema,
       t.table_name
from v_catalog.tables t
left join num_rows nr
          on nr.schema_name = t.table_schema
          and nr.table_name = t.table_name
where nr.rows is null or nr.rows < 1
group by t.table_schema,
         t.table_name
order by table_schema,
         table_name;

mosheg

The following does NOT cover situations with deleted rows:

select table_schema, table_name
from v_catalog.tables
EXCEPT
select anchor_table_schema, anchor_table_name
from v_monitor.column_storage;

mosheg

If you have many rows in your tables, consider one of the following options:
1. Add a column to specific relevant tables with default values as shown below:
CREATE TABLE X.A (f1 int, f2 int, f3 varchar(10), f4 VARCHAR(50),TenDigitsOrMore BOOLEAN DEFAULT regexp_like(f4,'\d{10}'));
And later you can do faster queries like: SELECT count(1) from X.A WHERE TenDigitsOrMore;
2. Create TEXT INDEX as shown here:
https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATETEXTINDEX.htm
3. Use the Free Text Search with Vertica/Lucene++ UDx Integration package by Maurizio Felici
4. Use the following if it is fast enough to satisfy your need:

CREATE SCHEMA X;
CREATE TABLE X.A (f1 int, f2 int, f3 varchar(10), f4 VARCHAR(50));
CREATE TABLE X.B (f1 int, f2 int, f3 varchar(10), f4 VARCHAR(50));
CREATE TABLE X.C (f1 int, f2 int, f3 varchar(10), f4 VARCHAR(50));
INSERT INTO X.A(f4) values ('lala123456789012xyz');
INSERT INTO X.B(f4) values ('lala1234567890xyz');
INSERT INTO X.B(f4) values ('lala123456789xyz');
COMMIT;
\! 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

Query output:
X.A.f4
X.B.f4

Jim_Knicely

@SK21 - For fun, here is another way of listing all tables that have 0 records:

dbadmin=> CREATE TABLE I_have_no_data(c1 INT);
CREATE TABLE

dbadmin=> \! vsql -Atc "SELECT 'SELECT ''' || table_schema || '.' || table_name || ''' table_name FROM ' || table_schema || '.' || table_name || ' HAVING COUNT(*) = 0;' FROM tables WHERE NOT is_system_table ORDER BY 1;" | vsql -At
public.I_have_no_data

SK21

That's a spot on query @mosheg .Thank you so much.
Can you help me on this also?
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.
I know that jim has answered their https://forum.vertica.com/discussion/242000/need-to-retrieve-particular-string-from-all-tables-and-get-table-names#latest.......................but its not suiting all my scenarios.

Let me make you understand here Suppose 2 schemas X,Y and two tables A,B in those schemas respectively
X.A table has a column named 'phone' with a 12 digit phone number.
X.B table has a column named 'mobile' with a 10 digit number.
Basically i want to list all the table names with their schema who have 10 or 10 plus numbers in any column (Columns can be of different names in different tables).