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.

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.