Question

这可能听起来不现实,但我最近不得不研究一个系统的数据库结构,而我却对我感到有些不安,因为我想找到与某个表格有联系的所有栏目(见原始杰夫没有建立明确的关系,而是把关系列入一栏!)

例如,假定指<代码> 括号>的所有栏目。作为记录两个领域之间关系的手段,有<代码>table.somefield_will,我如何利用标准指挥/内部设计的职能,发现所有这些关系(和有关表格)?

欢迎任何针对MySQL或PogreSQL的解决办法。

注:

am not looking for patterns in the data, but in the meta-data.
I know I can dump the db structure and search for the results there, but I don t want to go out of the db-shell to find these relationships (assume am working in say psql, and don t want to exit)

Answer 1

在PostgreSQL,请查询系统数据表。

你还可以询问Schema的信息,该信息包括提供显示信息的标准方式的意见。记录系统数据集通常要更快得多,尽管如此。

寻找一个特定孩子:<代码>/代码>。页: 1

SELECT n.nspname AS schema_name
     , c.relname AS table_name
     , a.attname AS column_name
FROM   pg_catalog.pg_attribute a
JOIN   pg_catalog.pg_class c ON c.oid = a.attrelid
JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE  a.attname ~~ E %\_parent 
AND    NOT a.attisdropped
AND    c.relkind =  r 
AND    nspname !~~ E pg\_% ;

根据说明查找<>all相关栏目:

SELECT n.nspname AS parrent_schema
     , c.relname AS parrent_table
     , a.attname AS parrent_column
     , n1.nspname AS child_schema
     , c1.relname AS child_table
     , a1.attname AS child_column
FROM   pg_catalog.pg_attribute a
JOIN   pg_catalog.pg_class c ON c.oid = a.attrelid
JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
JOIN   pg_catalog.pg_attribute a1 ON a.attname = substring(a1.attname,  _(.*?)$ )
JOIN   pg_catalog.pg_class c1 ON c1.oid = a1.attrelid
JOIN   pg_catalog.pg_namespace n1 ON n1.oid = c1.relnamespace
WHERE  c.relkind =  r 
AND    c1.relkind =  r 
AND    n.nspname !~~ E pg\_% 
AND    n1.nspname !~~ E pg\_% 
AND    NOT a.attisdropped
AND    NOT a1.attisdropped
-- AND    n.nspname =  public    -- to narrow it down to a specific schema
-- AND    n1.nspname =  public    -- to narrow it down to a specific schema
ORDER  BY 1,2,3,4,5,6

This assumes that the parrent columns don t have _ in the name.
The key element is the join condition:

a.attname = substring(a1.attname,  _(.*?)$ )

Answer 2

Try query querying the information_schema.columns view. 它在MySQL工作,但我没有在Pogres进行审判。 <代码>信息_schema 该表是ANSIQ标准的一部分,因此,邮政局很可能也会这样做。

Other views in information_schema include information_schema.tables, which is useful as well

Answer 3

如果你使用MySQL,你可以为此利用数据库信息。该数据库载有该服务器数据库的所有元数据。就我所知,每个数据库用户都必须能够查阅该数据库。

友情链接