Jump to content
Arasu

Search specific value in all fields of all tables

Recommended Posts

I have around 2000 tables in my oracle database. whatever the user enters in search page, I should search that value in all fields of all tables of database. My database contains several millions of records. Performance is an import point to consider. I am looking for a solution. Could someone help

Share this post


Link to post
Share on other sites

Hi Arasu, and welcome to the forum!

You can easily achieve this using PL/SQL by looping through with a FOR loop, querying all_tab_columns, for all columns that have a string datatype.

For each table name, you use “execute immediate”, building the SQL statement that looks for your string, using the table_name and column_name.

See example here:

https://lalitkumarb.wordpress.com/2015/01/06/sql-to-search-for-a-value-in-all-columns-of-all-atbles-in-an-entire-schema/

As to performance, there will be a full table scan of strings in all_tab_columns.

This can be made faster using parallel hints in the generated SQL.

Good luck!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×