Postgresql search all tables for value

by

To search all tables in a PostgreSQL database for a specific value, you can use the following SQL query:

“`sql
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema = ‘public’
AND table_catalog = ‘your_database_name’
AND table_name IN (
SELECT table_name
FROM information_schema.columns
WHERE table_schema = ‘public’
AND column_name ILIKE ‘%your_search_value%’
);
“`

Here’s an explanation of the steps involved in the query:

1. The `information_schema.columns` table is a system catalog table that contains information about all columns in all tables in the database.

2. The outer query selects the `table_name` and `column_name` from the `information_schema.columns` table.

3. The `table_schema = ‘public’` condition ensures that we are searching only in the public schema. Change `’public’` to the desired schema name if you want to search in a specific schema.

4. The `table_catalog = ‘your_database_name’` condition specifies the name of the database where you want to search. Replace `’your_database_name’` with the actual name of your database.

5. The inner subquery selects the `table_name` from the `information_schema.columns` table, but only for those tables that have at least one column containing the search value (`column_name ILIKE ‘%your_search_value%’`).

Example:

Let’s say we have a PostgreSQL database with the following tables and columns:

– Table: `users`
– Columns: `id`, `name`, `email`

– Table: `orders`
– Columns: `order_id`, `user_id`, `product_name`

If we want to search for the value `’john’`, the query would be:

“`sql
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema = ‘public’
AND table_catalog = ‘your_database_name’
AND table_name IN (
SELECT table_name
FROM information_schema.columns
WHERE table_schema = ‘public’
AND column_name ILIKE ‘%john%’
);
“`

The result of this query would be:

“`html

Table Name Column Name
users name
users email

“`

This HTML content represents a table where each row contains the name of the table and the name of the column that contains the search value. Note that you can style this HTML content according to your requirements.

Leave a comment