snippetMajor

How to write a query to find all tables in a db that have a specific column name

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

tableshowallcolumnquerywritethatnamefindspecific

Problem

I've got a database with about 100 tables and I need to build a join query to get specific data from two of them. I know one but not the other. Basically I need something like:

select from where exists table.column name;

How can I do this?

Solution

Using information_schema

This is the standards-compliant cross-RDBMS way to do it.

SELECT table_catalog, table_schema, table_name, column_name
FROM INFORMATION_SCHEMA.columns
WHERE column_name = '';

You can see this documented

PostgreSQL

SQL Server

MySQL 8.0

Code Snippets

SELECT table_catalog, table_schema, table_name, column_name
FROM INFORMATION_SCHEMA.columns
WHERE column_name = '<your column name>';

Context

StackExchange Database Administrators Q#44785, answer score: 23

Revisions (0)

No revisions yet.