How to search any string in whole database?

SK21

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

mosheg

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_Knicely

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;

SK21

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

SK21

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.

SK21

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.

SK21

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

Jim_Knicely

@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