Bug 6746

Summary: Extend sys.sequences with columns showing the currentvalue and datatype
Product: SQL Reporter: Martin van Dinther <martin.van.dinther>
Component: allAssignee: SQL devs <bugs-sql>
Status: NEW ---    
Severity: enhancement    
Priority: Normal    
Version: 11.33.3 (Apr2019)   
Hardware: All   
OS: All   

Description Martin van Dinther cwiconfidential 2019-08-13 20:42:12 CEST
User-Agent:       Mozilla/5.0 (Windows NT 6.1; rv:68.0) Gecko/20100101 Firefox/68.0
Build Identifier: 

Request to extend the system table sys.sequences with a column 'currentvalue' to also show the currentvalue for each sequence.

When implementing this request combine it with adding the column 'datatype' as described in bug 6745

Reproducible: Always

Steps to Reproduce:
select * from sys.sequences;
\d sequences
Actual Results:  
sql>select * from sys.sequences;
+-------+-------+-----------+-------+-------+-------+--------+--------+-------+
| id    | schem | name      | start | minva | maxva | increm | cachei | cycle |
:       : a_id  :           :       : lue   : lue   : ent    : nc     :       :
+=======+=======+===========+=======+=======+=======+========+========+=======+
| 22293 |  2000 | seq_22285 |     1 |     0 |     0 |      1 |      1 | false |
+-------+-------+-----------+-------+-------+-------+--------+--------+-------+
1 tuple
sql>

sql>\d sequences
CREATE TABLE "sys"."sequences" (
        "id"        INTEGER,
        "schema_id" INTEGER,
        "name"      VARCHAR(256),
        "start"     BIGINT,
        "minvalue"  BIGINT,
        "maxvalue"  BIGINT,
        "increment" BIGINT,
        "cacheinc"  BIGINT,
        "cycle"     BOOLEAN
);
sql>


Expected Results:  
sql>select * from sys.sequences;
+-------+-----------+-----------+-------+----------+----------+--------------+-----------+----------+-------+
| id    | schema_id | name      | start | minvalue | maxvalue | currentvalue | increment | cacheinc | cycle |
+=======+===========+===========+=======+==========+==========+==============+===========+==========+=======+
| 22293 |      2000 | seq_22285 |     1 |        0 |        0 |           11 |         1 |        1 | false |
+-------+-----------+-----------+-------+----------+----------+--------------+-----------+----------+-------+
1 tuple

sql>\d sequences
CREATE TABLE "sys"."sequences" (
        "id"        INTEGER,
        "schema_id" INTEGER,
        "name"      VARCHAR(256),
        "datatype"  VARCHAR(256),
        "start"     BIGINT,
        "minvalue"  BIGINT,
        "maxvalue"  BIGINT,
        "currentvalue" BIGINT,
        "increment" BIGINT,
        "cacheinc"  BIGINT,
        "cycle"     BOOLEAN
);
sql>

Alternatively we could add a system view to derive the currentvalue column:
CREATE OR REPLACE VIEW "sys"."sequences_vw" AS
select q.id, schema_id, s.name as schema, q.name, start, minvalue, maxvalue, get_value_for(s.name, q.name) as currentvalue, increment, cacheinc, cycle
from "sys"."sequences" q join "sys"."schemas" s on s.id = q.schema_id order by schema, name;