Postgresql: List All Sequences

list_seqs.sql

-- List all sequences and their current values, excluding system schemas
SELECT 
    n.nspname as schema,
    s.relname as sequence_name,
    t.relname as table_name,
    a.attname as column_name,
    pg_sequence_last_value(s.oid) as last_value
FROM pg_class s
JOIN pg_namespace n ON n.oid = s.relnamespace
JOIN pg_depend d ON d.objid = s.oid 
    AND d.deptype = 'a'
JOIN pg_class t ON t.oid = d.refobjid
JOIN pg_attribute a ON a.attrelid = t.oid 
    AND a.attnum = d.refobjsubid
WHERE s.relkind = 'S'
    AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schema, sequence_name;

list_seqs_fn.sql

CREATE OR REPLACE FUNCTION list_seqs()
RETURNS TABLE (
    schema_name text,
    sequence_name text,
    table_name text,
    column_name text,
    last_value bigint
) 
LANGUAGE SQL
STABLE
AS $$
    SELECT 
        n.nspname as schema_name,
        s.relname as sequence_name,
        t.relname as table_name,
        a.attname as column_name,
        pg_sequence_last_value(s.oid) as last_value
    FROM pg_class s
    JOIN pg_namespace n ON n.oid = s.relnamespace
    JOIN pg_depend d ON d.objid = s.oid 
        AND d.deptype = 'a'
    JOIN pg_class t ON t.oid = d.refobjid
    JOIN pg_attribute a ON a.attrelid = t.oid 
        AND a.attnum = d.refobjsubid
    WHERE s.relkind = 'S'
        AND n.nspname NOT IN ('pg_catalog', 'information_schema')
    ORDER BY schema_name, sequence_name;
$$;

-- To use the function, simply call:
-- SELECT * FROM list_seqs();