search for column name in psql 1

search for column name in psql

select t.table_schema,
       t.table_name
from information_schema.tables t
inner join information_schema.columns c on c.table_name = t.table_name 
                                and c.table_schema = t.table_schema
where c.column_name = 'last_name'
      and t.table_schema not in ('information_schema', 'pg_catalog')
      and t.table_type = 'BASE TABLE'
order by t.table_schema;

Here is what the above code is Doing:
1. We’re querying the information_schema.tables table to get all the tables in the database.
2. We’re querying the information_schema.columns table to get all the columns in the database.
3. We’re joining the tables and columns tables on the table_name column.
4. We’re filtering the results to only include tables that have a column named last_name.
5. We’re filtering the results to only include tables that are in the public schema.
6. We’re filtering the results to only include tables that are base tables.
7. We’re ordering the results by the table_schema column.

The results of the query are:

table_schema table_name
public customers
public employees
public offices
public orderdetails
public orders
public payments
public productlines
public products

Similar Posts