0

is there a way to search a pattern string in all columns, all tables, in a Oracle database? Should be case-insensitive. For example, I'd like to find in which tables/columns there is the string 'alpha'. Strings with values: 'alphabeth', '001alpha', 'alpha001' should be returned also.

Has anyone idea how to write a query like this?

Thanks a lot in advance.

Luis

Ciupaz
  • 639
  • 1
  • 10
  • 19

1 Answers1

1

A query ? Sure NO. A PL/SQL procedure using the meta data found in user/all_tab_columns, EXECUTE IMMEDIATE to execute dynamic queries using INSTR or LIKE with COLLATE BINARY_AI or BINARY_CI. ("all columns": no, only the one of string types: CLOB, CHAR, VARCHAR2, NVARCHAR2, ... BLOB may be more annoying because some are storing JSON there... but no way no know without knowledge of the application, or maybe by checking the presence of a IS JSON constraint on the column... )

But seriously what could be the business need for such requirement? Forensic?

p3consulting
  • 2,721
  • 2
  • 12
  • 10
  • 1
    You can do it with a query via a (dynamic, but not PL/SQL) XML query - Lalit shows some options in the linked question. – Alex Poole Sep 22 '22 at 17:34