How to find out all the tables with no data.

SK21SK21 Vertica Customer
edited December 2020 in General Discussion

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.

Best Answers

  • Options
    moshegmosheg Vertica Employee Administrator
    Answer ✓
    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,
    select t.table_schema,
    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,
    order by table_schema,
  • Options
    moshegmosheg Vertica Employee Administrator
    edited December 2020 Answer ✓

    The following does NOT cover situations with deleted rows:

    select table_schema, table_name
    from v_catalog.tables
    select anchor_table_schema, anchor_table_name
    from v_monitor.column_storage;
  • Options
    moshegmosheg Vertica Employee Administrator
    edited January 2021 Answer ✓

    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:
    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 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');
    \! 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:
  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited January 2021 Answer ✓

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

    dbadmin=> CREATE TABLE I_have_no_data(c1 INT);
    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


  • Options
    SK21SK21 Vertica Customer

    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).

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file