Wednesday, August 7, 2013

Text search using sql in RDBMS database

To search one keyword across all available tables in RDBMS is not a simple task and may require specialised tools like enterprise search.

Here i am listing one solution which involves little bit of manual effort but it is the simplest possible solution.

The trick is to view the database metadata and get all the tables and columns from the database.
This query can be optimised in many different ways as per the requirement/database or need.

The below is the query for postgres database and using pgAdmin tool. This assumes that the schema name is 'public' feel free to change it according to your need.

SELECT ('select ' || column_name ||  ' from ' || table_name || ' where '||  column_name ||  ' like ''%?%''; ')  
FROM information_schema.columns c
WHERE c.table_name in (
SELECT table_name
FROM information_schema.tables where table_schema = 'public'
) and c.data_type='text'

Once you run this query you will get a big list of select statements which you need to manually copy and paste to pgAdmin sql tool or notepad.

Replace all the generated ? with the text query you want to search.

Hope this little trick is useful and save your life one day, feel free to update the query and post your comments with your use case and the actual query you end up using.

I will create some tool and share it if someone ask :)