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

How to search any string in whole database?

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 Employee
    edited January 12 Accepted 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 Administrator
    edited January 12 Accepted 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

  • @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.......

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

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

    [[email protected] ~]$ vsql -Udbadmin -w '[email protected]@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 [[email protected] ~]$ vsql -Udbadmin -w '[email protected]@1'
    and i got connected to db.Kindly suggest what changes should i make.

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

  • Jim_KnicelyJim_Knicely 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

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