PostgreSQL：SQL脚本，用于获取具有特定列作为外键的所有表的列表

本文介绍了PostgreSQL：SQL脚本，用于获取具有特定列作为外键的所有表的列表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用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'