How to search any string in whole database?

SK21SK21 Vertica Customer

Suppose my string is '1234' and in want to search it every possible table in any schema.
Is their any query to do it?

Best Answers

  • moshegmosheg Vertica Employee Administrator
    edited January 2021 Answer ✓

    Try this:

    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 ('lala1234567xyz');
    INSERT INTO X.B(f4) values ('lala123xyz');
    INSERT INTO X.B(f4) values ('lala123 4xyz');
    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 || ', ''1234''));' FROM columns WHERE NOT is_system_table AND data_type_id = 9 ORDER BY table_schema, table_name, ordinal_position;" | vsql -At
    

    The output: X.A.f4

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited January 2021 Answer ✓

    The example that @mosheg provdided is piping all of those SQL statements you get in DbVisualizer back into vsql so that they run adn produce your answer.

    You could try running this in DbVisualizer to create a single SQL query, but then you will need to cut and past it into another DbVisualizer Window to run it.

    SELECT 'SELECT ''' || table_schema || '.' || table_name || '.' || column_name || ''' FROM dual WHERE EXISTS (SELECT NULL FROM ' || table_schema || '.' || table_name || ' WHERE regexp_like(' || column_name || ', ''1234'')) ' || CASE WHEN LEAD(table_schema) OVER(ORDER BY table_schema, table_name, ordinal_position) IS NULL THEN ';' ELSE 'UNION ALL' END FROM columns WHERE NOT is_system_table AND data_type_id = 9 ORDER BY table_schema, table_name, ordinal_position;
    
    

Answers

  • SK21SK21 Vertica Customer

    @mosheg i am trying this one on dbvisualizer
    SELECT 'SELECT ''' || table_schema || '.' || table_name || '.' || column_name || ''' FROM dual WHERE EXISTS (SELECT NULL FROM ' || table_schema || '.' || table_name || ' WHERE regexp_like(' || column_name || ', ''1234''));' FROM columns WHERE NOT is_system_table AND data_type_id = 9 ORDER BY table_schema, table_name, ordinal_position

    But its just returning the queries where it is searching (may be i am doing something wrong)
    SELECT 'abc.xyz.updated ' FROM dual WHERE EXISTS (SELECT NULL FROM abc.xyz WHERE regexp_like(updated city, '1234'));
    SELECT 'abc.xyz.Zone2' FROM dual WHERE EXISTS (SELECT NULL FROM abc.xyz WHERE regexp_like(Zone2, '1234'));
    ...
    ...
    etc.......

  • SK21SK21 Vertica Customer

    Thanks @mosheg and @Jim_Knicely .....in that case executing in putty makes more sense as the output query would be too long due to thousands of tables.

  • SK21SK21 Vertica Customer

    Good day @mosheg @Jim_Knicely so today i had a chance to execute this query in production

    But the query stucked here and i checked on another terminal from top command if the process is getting execute ...it doesent seem like to be.

    [dbadmin@verticadb-1 ~]$ vsql -Udbadmin -w 'P@sword@1' -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 || ', ''636344''));' FROM columns WHERE NOT is_system_table AND data_type_id = 9 ORDER BY table_schema, table_name, ordinal_position;" | vsql -At
    Password: NOTICE 2001:

    Also when i press enter on this terminal i got the following error
    vsql: FATAL 3781: Invalid username or password

    So i tried this [dbadmin@verticadb-1 ~]$ vsql -Udbadmin -w 'P@sword@1'
    and i got connected to db.Kindly suggest what changes should i make.

  • SK21SK21 Vertica Customer

    Ok i understood i did not add password and username after pipe in vsql

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    @SK21 - If you don't want to have the password show up on the command line, take advantage of the VSQL_PASSWORD environment variable.

    See:
    https://www.vertica.com/docs/latest/HTML/Content/Authoring/ConnectingToVertica/vsql/vsqlEnvironmentVariables.htm

Leave a Comment

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