我正在使用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脚本,用于获取具有特定列作为外键的所有表的列表
发布评论