在Postgresql中，如何找到包含单个表的主键或特定列的db的所有表?

How can I find all the tables of db that contains primary key or particular column of a single table in Postgresql Database....Means a column of a perticular table that is included in many tables either as foreign key or non - foreign key...column can be primary key or a non - primary key....

我怎么能找到数据库的所有表,其中包含一个表的主键或特定列Postgresql数据库....是指在许多表中作为外键或非外键的一列。列可以在非主键或主键....

1 个解决方案

#1

Use below Query to find particular column of a single table:

使用以下查询查找单个表的特定列:

SELECT table_name
FROM information_schema.columns
WHERE table_schema = 'public'
  AND column_name   = 'YOUR_COLUMN_NAME'

Use below Query to find List of all the tables of db that contains primary key

使用下面的查询查找包含主键的db所有表的列表

 SELECT  t.table_catalog, 
         t.table_schema, 
         t.table_name, 
         kcu.constraint_name, 
         kcu.column_name, 
         kcu.ordinal_position 
FROM    INFORMATION_SCHEMA.TABLES t 
         LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
                 ON tc.table_catalog = t.table_catalog 
                 AND tc.table_schema = t.table_schema 
                 AND tc.table_name = t.table_name 
                 AND tc.constraint_type = 'PRIMARY KEY' 
         LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
                 ON kcu.table_catalog = tc.table_catalog 
                 AND kcu.table_schema = tc.table_schema 
                 AND kcu.table_name = tc.table_name 
                 AND kcu.constraint_name = tc.constraint_name 
WHERE   t.table_schema NOT IN ('pg_catalog', 'information_schema') 
ORDER BY t.table_catalog, 
         t.table_schema, 
         t.table_name, 
         kcu.constraint_name, 
         kcu.ordinal_position;

Use below Query to find List of table having Foreign Key in another Table

使用下面的查询查找另一个表中具有外键的表的列表

 SELECT  t.table_name as FK_Table, tc.constraint_name,tc.constraint_type,ccu.table_name as PK_Table
FROM    INFORMATION_SCHEMA.TABLES t 
         INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
                 ON tc.table_catalog = t.table_catalog 
                 AND tc.table_schema = t.table_schema 
                 AND tc.table_name = t.table_name 

         INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu 
                 ON ccu.constraint_name = tc.constraint_name
           /* Remove comments for check based on CONSTRAINT'name OR 'PRIMARY_KEY' name */ 
                --and ccu.constraint_name = 'CONSTRAINT_NAME' 
                --and  AND ccu.column_name = 'COLUMN_NAME'
WHERE   t.table_schema NOT IN ('pg_catalog', 'information_schema')

#1