查詢指定使用者的unique,primary索引名/鍵值

DBAGPT發表於2024-05-01
索引

--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;

相關文章