CODEWITHSUNDEEP
sqlpostgresql
sergzach
sergzach

Reputation: 6764

How to select all table names where a 'user_id' column is?

I'd like to select all table names where one of columns has a name user_id.

Is it possible in PostgreSQL?

Upvotes: 3

Views: 1558

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659217

Make sure to include the schema name. Your table name can be ambiguous without it. May even lead to harmful confusion:

SELECT table_schema, table_name
FROM information_schema.columns
WHERE column_name = 'user_id';

The information schema is only good if you need cross-RDBMS portability, which is rarely needed and rarely works. I'd rather query the catalog tables directly. Faster and shorter:

SELECT attrelid::regclass
FROM   pg_attribute
WHERE  attname = 'user_id';

And be sure that you are connected to the same database.

Upvotes: 1

peter.petrov
peter.petrov

Reputation: 39477

Try this:

select table_name
from information_schema.columns
where column_name = 'user_id'

It should work OK.

Upvotes: 3

Related Questions