PostgreSQL：SQL脚本，用于获取具有特定列作为外键的所有表的列表 [英] PostgreSQL: SQL script to get a list of all tables that has a particular column as foreign key

问题描述

我正在使用PostgreSQL，并且尝试列出表中具有特定列的所有表作为外键/引用。能做到吗？我确定此信息存储在 information_schema 中的某个位置，但我不知道如何开始查询。

I'm using PostgreSQL and I'm trying to list all the tables that have a particular column from a table as a foreign-key/reference. Can this be done? I'm sure this information is stored somewhere in information_schema but I have no idea how to start querying it.

推荐答案

select R.TABLE_NAME
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK
    on U.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG
    and U.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA
    and U.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE R
    ON R.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
    AND R.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
    AND R.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
WHERE U.COLUMN_NAME = 'a'
  AND U.TABLE_CATALOG = 'b'
  AND U.TABLE_SCHEMA = 'c'
  AND U.TABLE_NAME = 'd'

这将使用完整的目录/模式/名称三元组从所有3个information_schema视图中标识数据库表。您可以根据需要删除一个或两个。

This uses the full catalog/schema/name triplet to identify a db table from all 3 information_schema views. You can drop one or two as required.

查询列出对表'd'中的列'a'具有外键约束的所有表

The query lists all tables that have a foreign key constraint against the column 'a' in table 'd'

这篇关于PostgreSQL：SQL脚本，用于获取具有特定列作为外键的所有表的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！