如何选择'user_id'列所在的所有表名？

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

我想选择所有表名,其中一列的名称为user_id。

Is it possible in PostgreSQL?

在PostgreSQL中有可能吗?

2 个解决方案

#1

Try this:

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

It should work OK.

它应该工作正常。

#2

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:

只有在需要跨RDBMS可移植性时,信息模式才有用,这很少需要并且很少有效。我宁愿直接查询目录表。更快更短:

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

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

并确保您连接到同一个数据库。

#1