How to find all table dependencies in Vertica

Hi,
I would like to find all the scripts & views which uses the Vertica table. I need this as there are lots of tables which are not in use in our database. If the table is not used in any scripts / views, then we are planning to delete the table in future.

Thanks in Advance

Comments

  • each time a projection is used we write a log to dc_projections_used
    select * from dc_projections_used;

    increase the retention of the above table.

    select set_data_collector_policy('ProjectionsUsed', .., ..);
    select get_data_collector_policy('ProjectionsUsed');

    This will give you a list of projections used, and how often.
    The inverse of this set are projections that have not been used.

  • Thanks. But, I am trying to find the scripts (programs) used to update / fetch the data in the table.

  • this table (dc_projection_used) has the tx_id and statement_id, which you can join with dc_request_issued to find the query that touched this projection.

  • Thanks. Is it possible to find the shell script (.sh) or .SQL file which calls the query.

  • Jim_KnicelyJim_Knicely Administrator

    There is a CLIENT_LABEL column in the DC_REQUESTS_ISSUED table...

    dbadmin=> select get_client_label();
     get_client_label
    ------------------
    
    (1 row)
    
    dbadmin=> select set_client_label('Connection from shell script /home/dbadmin/my_test.sh');
                                 set_client_label
    ---------------------------------------------------------------------------
     client_label set to Connection from shell script /home/dbadmin/my_test.sh
    (1 row)
    
    dbadmin=> select count(*) from test;
     count
    -------
         1
    (1 row)
    
    dbadmin=> select request from dc_requests_issued where client_label = 'Connection from shell script /home/dbadmin/my_test.sh';
                                                           request
    ----------------------------------------------------------------------------------------------------------------------
     select count(*) from test;
     select request from dc_requests_issued where client_label = 'Connection from shell script /home/dbadmin/my_test.sh';
    (2 rows)
    

Leave a Comment

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