查询指定用户的unique,primary索引名/键值

--1.SQL用 postgres账户查询 PostgreSQL 中指定DB以及schema下唯一索引的信息，按照表名：索引名：索引键值 并按表名排序输出
SELECT
    t.tablename AS table_name,
    i.indexname AS index_name,
    string_agg(a.attname, ', ' ORDER BY a.attnum) AS index_keys
FROM
    pg_indexes i
    JOIN pg_class t ON i.tablename = t.relname
    JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(i.indkey)
WHERE
    t.relkind = 'r' -- 可选：限制为普通表，排除视图和其他特殊关系类型
    AND i.schemaname = 'your_schema' -- 替换为要查询的模式名
    AND i.indexdef LIKE 'CREATE UNIQUE%'
GROUP BY
    t.tablename, i.indexname
ORDER BY
    t.tablename;



--2.SQL用 postgres账户查询 PostgreSQL 中指定DB以及schema下主键索引的信息，按照表名：索引名：索引键值 并按表名排序输出
SELECT
    t.tablename AS table_name,
    i.indexname AS index_name,
    string_agg(a.attname, ', ' ORDER BY a.attnum) AS index_keys
FROM
    pg_indexes i
    JOIN pg_class t ON i.tablename = t.relname
    JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(i.indkey)
WHERE
    t.relkind = 'r' -- 可选：限制为普通表，排除视图和其他特殊关系类型
    AND i.schemaname = 'your_schema' -- 替换为要查询的模式名
    AND i.indexdef LIKE 'CREATE UNIQUE%PRIMARY KEY%'
GROUP BY
    t.tablename, i.indexname
ORDER BY
    t.tablename;



--3.SQL用sys账户查询Oracle中指定schema下唯一索引的信息，按照表名：索引名：索引键值 并按表名排序输出
SELECT
    c.table_name,
    i.index_name,
    listagg(c.column_name, ', ') WITHIN GROUP (ORDER BY c.column_position) AS index_keys
FROM
    all_indexes i
    JOIN all_ind_columns c ON i.index_name = c.index_name AND i.table_name = c.table_name
WHERE
    i.owner = 'your_schema' -- 替换为要查询的模式名
    AND i.uniqueness = 'UNIQUE'
GROUP BY
    c.table_name, i.index_name
ORDER BY
    c.table_name;



--4.SQL用sys账户查询Oracle中指定schema下主键索引的信息，按照表名：索引名：索引键值 并按表名排序输出
SELECT
    c.table_name,
    i.index_name,
    listagg(c.column_name, ', ') WITHIN GROUP (ORDER BY c.column_position) AS index_keys
FROM
    all_constraints pk
    JOIN all_indexes i ON pk.index_name = i.index_name AND pk.table_name = i.table_name
    JOIN all_cons_columns c ON pk.constraint_name = c.constraint_name AND pk.table_name = c.table_name
WHERE
    pk.owner = 'your_schema' -- 替换为要查询的模式名
    AND pk.constraint_type = 'P'
GROUP BY
    c.table_name, i.index_name
ORDER BY
    c.table_name;