How to find all table dependencies in Vertica

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


  • Options
    skeswaniskeswani - Select Field - Employee

    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.

  • Options

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

  • Options
    skeswaniskeswani - Select Field - Employee

    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.

  • Options

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

  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator

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

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

    Thanks Jim

Leave a Comment

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