在postgresql、oracle、mysql中查一张表的所有分区名、主键和主键字段及非空字段

1.postgresql数据库

查询某一张表的主键名:

SELECT relname as partition_name, conname as primary_key
FROM pg_class c
JOIN pg_constraint con ON con.conrelid = c.oid
WHERE conrelid = '键入表名'::regclass
AND relkind = 'p'

查询某一张表的主键字段:

SELECT a.attname as primary_key_field
FROM pg_index i
JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = any(i.indkey)
WHERE i.indrelid = '键入表名'::regclass AND i.indisprimary

查询某一张表的非空字段:

SELECT column_name
FROM information_schema.columns
WHERE table_name = '键入表名' AND is_nullable='NO'

2.oracle数据库

查询某一张表的主键名:

SELECT partition_name, constraint_name as primary_key
FROM user_part_tables
WHERE table_name = '键入表名';

查询某一张表的主键字段:

SELECT column_name
FROM user_cons_columns
WHERE table_name = '键入表名'
AND constraint_name = (
    SELECT constraint_name
    FROM user_part_tables
    WHERE table_name = '键入表名'
);

查询某一张表的非空字段:

SELECT column_name
FROM user_tab_columns
WHERE table_name = 'table_name'
AND nullable = 'N';

3.mysql数据库

查询某一张表的所有分区名称和主键:

SELECT partition_name, constraint_name as primary_key
FROM information_schema.partitions
WHERE table_name = '键入表名';

查询某一张表的主键字段:

SELECT column_name
FROM information_schema.key_column_usage
WHERE table_name = '键入表名'
AND constraint_name = (
    SELECT constraint_name
    FROM information_schema.partitions
    WHERE table_name = '键入表名'
);

查询某一张表的非空字段:

SELECT column_name
FROM information_schema.columns
WHERE table_name = '键入表名'
AND is_nullable = 'NO';