Finding all tables with a specific column name (postgres)
Tags: sql, postgres
Published: 2022-02-14 10:51:00

## The information schema

> The information schema consists of a set of views that contain information about the objects defined in the current database. 

This is how Postgres defines the information schema. If you look at the [official documentation](https://www.postgresql.org/docs/current/information-schema.html), you will find two relevant views in this schema:

- the [`tables` view](https://www.postgresql.org/docs/current/infoschema-tables.html) that contains all tables and views defined in the database
- and the [`columns` view](https://www.postgresql.org/docs/current/infoschema-columns.html) that contains all columns (or view columns) defined in the database

These two views will give you access to information on the tables and the columns in our database, so let's see how we can use them:

##  The query

Let's say we want to get all tables with a `student_id` column. 

If you are working with a single `public` schema (the default schema that Postgres will use to hold your tables), this query will do the trick:

```sql
SELECT 
  tables.tables_name
FROM information_schema.tables AS tables
JOIN information_schema.columns AS columns
  ON columns.table_name = tables.table_name
  AND columns.table_schema = tables.table_schema
WHERE columns.column_name = 'student_id'
  AND tables.table_schema = 'public'
  AND tables.table_type = 'BASE TABLE'
ORDER BY tables.table_name
```

A few notes on this query:

- To access any of the views, you need to add `information_schema` as a prefix to the view name;

- For the query to retrieve all tables with a `student_id` column it will have to join the two views that hold this information - `information_schema.tables` and `information_schema.columns`;

- Postgres has its own internal schemas that you will want to exclude (e.g. the `information_schema` itself and the `pg_catalog` schema). In this case, we want the `public` schema only;

- You can filter `tables` by table type: `BASE TABLE` for a persistent base table (the normal table type), `VIEW` for a view, `FOREIGN TABLE` for a foreign table, or `LOCAL TEMPORARY` for a temporary table. In this case, I only want the `BASE TABLE`.

### Example of the query output:

| | table_name |
| --- | ---|
| 1 |  course_statistics |
| 2 | student_classes |

## Multiple schemas

If you are working with multiple schemas, the query needs some adjustments.

You can filter the results to a specific schema or it might be useful to select the table names together with the corresponding schema name. In the latter case, you will need to add `tables.table_schema` to the `SELECT` statement and, instead of choosing a specific schema, exclude Postgres internal schemas:

```sql       
SELECT
   tables.table_schema,
   tables.table_name
FROM information_schema.tables as tables
JOIN information_schema.columns AS columns
  ON columns.table_name = tables.table_name
  AND columns.table_schema = tables.table_schema
WHERE columns.column_name = 'student_id'
  AND tables.table_schema not in ('information_schema', 'pg_catalog')
  AND tables.table_type = 'BASE TABLE'
ORDER by tables.table_schema
```

### Example of the query output:

| | table_schema | table_name |
| --- | --- | ---|
| 1 |  public |  student_classes |
| 2 | public | course_statistics |
| 3 | tenant_1 | student_classes |
| 4 | tenant_1 | course_statistics |

## Conclusion

The `information_schema` contains metadata about the data objects stored in a database. It provides plenty of information not only on tables and columns but also permissions, triggers, constraints, and others. 

The example I've used in this article is a real case example. I have recently had to run a migration where all data of a student had to be migrated to another student. So instead of manually checking and writing down all tables with a student reference, I queried `information_schema` and was then able to iterate each one to perform an update.

> The information schema views do not, however, contain information about PostgreSQL-specific features; to inquire about those you need to query the system catalogs or other PostgreSQL-specific views.

Whether you need this kind of data for information purposes, migrations, or other actions, know that you will most probably find the information you need by querying Postgres internal schemas.