Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to find out all the tables with no data.

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

  • moshegmosheg Employee
    Accepted 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,
                 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;
    
  • moshegmosheg Employee
    edited December 2020 Accepted Answer

    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;
    
  • moshegmosheg Employee
    edited January 2 Accepted 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:
    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_KnicelyJim_Knicely Administrator
    edited January 2 Accepted 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);
    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
    

Answers

  • 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
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.