find holes in id numeration

gistfile1.sql

EXECUTE BLOCK
  /* RETURNS(tbl VARCHAR(31), cnt INTEGER) */
AS
  DECLARE VARIABLE rn VARCHAR(31);
  DECLARE VARIABLE fn VARCHAR(31);
  DECLARE VARIABLE id INTEGER;
  DECLARE VARIABLE b INTEGER = 147000001;
  DECLARE VARIABLE e INTEGER = 147000001;
  DECLARE VARIABLE limit INTEGER;
  DECLARE VARIABLE d INTEGER;
  DECLARE VARIABLE c INTEGER = 0;
BEGIN
  SELECT GEN_ID(gd_g_unique, 0) FROM rdb$database
    INTO :limit;

  /* возможно в кэшах ИД на рабочих станциях сохранены интервалы */
  /* берем с большим запасом, чтобы избежать перекрытия ИД       */
  limit = :limit - 25000000;

  IF (:limit > 147000000) THEN
  BEGIN
    DELETE FROM gd_available_id;

    RDB$SET_CONTEXT('USER_SESSION', 'GD_CURRENT_ID', NULL);
    RDB$SET_CONTEXT('USER_SESSION', 'GD_LIMIT_ID', NULL);

    IF (g_his_create(0, 0) = 0) THEN
      EXCEPTION gd_e_exception 'Can''t create huge array';

    FOR
      SELECT
        rf.rdb$relation_name, LIST(TRIM(rf.rdb$field_name))
      FROM
        rdb$relation_fields rf
        JOIN rdb$relations r ON r.rdb$relation_name = rf.rdb$relation_name
        JOIN rdb$index_segments idxs ON idxs.rdb$field_name = rf.rdb$field_name
        JOIN rdb$indices idx ON idx.rdb$index_name = idxs.rdb$index_name
        JOIN rdb$relation_constraints rc ON rc.rdb$index_name = idx.rdb$index_name
          AND rc.rdb$relation_name = rf.rdb$relation_name
      WHERE
        rc.rdb$constraint_type = 'PRIMARY KEY'
        AND
        rf.rdb$relation_name <> 'GD_RUID'
        /*

        Тут должно быть условие на таблицы, в которых не соблюдается
        условие уникальности ИД.

        */
        AND
        r.rdb$system_flag = 0
        AND
        COALESCE(r.rdb$relation_type, 0) = 0
      GROUP BY
        1
      HAVING
        LIST(TRIM(rf.rdb$field_name)) = 'ID'
    INTO
      :rn, :fn
    DO BEGIN
      d = 0;
      EXECUTE STATEMENT
        'SELECT SUM(g_his_include(0, id)) FROM ' || :rn || ' WHERE id > 147000000'
      INTO :d;
      c = :c + COALESCE(:d, 0);

      /*
      tbl = :rn;
      cnt = :d;
      SUSPEND;
      */
    END
    
    SELECT
      SUM(g_his_include(0, movementkey))
    FROM
      inv_movement
    WHERE
      movementkey > 147000000
    INTO :d;

    /*
    tbl = 'total objects count';
    cnt = :c;
    SUSPEND;
    */

    WHILE ((:e < :limit) AND (:c > 0)) DO
    BEGIN
      IF (g_his_has(0, :e) <> 0) THEN
      BEGIN
        c = :c - 1;

        IF ((:e - :b) >= 100) THEN
        BEGIN
          INSERT INTO gd_available_id (id_from, id_to) VALUES (:b, :e - 1);
        END

        e = :e + 1;
        b = :e;
      END ELSE
      BEGIN
        e = :e + 1;
      END
    END

    IF (:c = 0) THEN
    BEGIN
      IF (:e < :limit) THEN
        e = :limit;

      IF ((:e - :b) >= 100) THEN
      BEGIN
        INSERT INTO gd_available_id (id_from, id_to) VALUES (:b, :e - 1);
      END
    END

    g_his_destroy(0);
  END
END