Subject Rebuild indices stored procedure
Author k_smith_000
Hello,
At page 263 of Operation Guide I've found script to generate
statements that rebuild indices:

SELECT 'ALTER INDEX ' || RDB$INDEX_NAME || ' ACTIVE;'
FROM RDB$INDICES
WHERE RDB$SYSTEM_FLAG = 0 OR RDB$SYSTEM_FLAG IS NULL;

I've tried to create stored procedure

CREATE PROCEDURE SERVICE_INDEX_REBUILD
AS
DECLARE VARIABLE S VARCHAR(200);
DECLARE VARIABLE S2 VARCHAR(200);
BEGIN

FOR select RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$SYSTEM_FLAG = 0
OR RDB$SYSTEM_FLAG IS NULL INTO :S DO
BEGIN
S = 'ALTER INDEX ' || s || 'inactive;';
EXECUTE STATEMENT :S;

S2 = 'ALTER INDEX ' || s || 'active;';
EXECUTE STATEMENT :S2;
END
SUSPEND;
END

Unfortunatelly when I try to execute procedure I got following error:

EXECUTE PROCEDURE SERVICE_INDEX_REBUILD;
Statement failed, SQLCODE = -607

unsuccessful metadata update
-MODIFY RDB$INDICESS failed
-action cancelled by trigger (2) to preserve data integrity
-Cannot deactivate index used by an Integrity Constraint


Best regards,
Kevin Smith