pears.TextIndexesReBuild

Forces the rebuilding of all manual and auto refresh text indexes

CREATE PROCEDURE "pears"."TextIndexesReBuild"()
BEGIN
  FOR "IndexLoop" AS "IndexCursor" no scroll cursor FOR
    SELECT "user_name","index_name","table_name","refresh_type"
      FROM "SYS"."SYSIDX" AS "i"
        JOIN "sys"."SYSTEXTIDX" AS "e" ON "e"."index_id" = "i"."object_id" AND "e"."sequence" = 1
        JOIN "sys"."SYSTAB" AS "t" ON "t"."table_id" = "i"."table_id"
        JOIN "sys"."SYSTEXTCONFIG" AS "f" ON "f"."object_id" = "e"."text_config"
        JOIN "sys"."SYSUSER" AS "u" ON "u"."user_id" = "t"."creator"
      WHERE "refresh_type" IN( 1,2 ) FOR READ ONLY
  do
    EXECUTE IMMEDIATE("string"('refresh text index [',"index_name",'] on [',"user_name",'].[',"table_name",'] force build'))
  END FOR
END
GO
 
COMMENT ON PROCEDURE "pears"."TextIndexesReBuild" IS 
	'Forces the rebuilding of all manual and auto refresh text indexes'
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."TextIndexesReBuild" IS 
{CREATE PROCEDURE TextIndexesReBuild 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
 () 
BEGIN
  FOR IndexLoop AS IndexCursor no scroll cursor FOR 
    SELECT
      user_name, index_name, TABLE_NAME, refresh_type
    FROM 
        SYS.SYSIDX AS i
            JOIN sys.SYSTEXTIDX AS e ON e.index_id = i.object_id AND e.[SEQUENCE] = 1
            JOIN sys.SYSTAB AS t ON t.table_id = i.table_id
            JOIN sys.SYSTEXTCONFIG AS f ON f.object_id = e.text_config 
            JOIN sys.SYSUSER AS u ON u.user_id = t.creator 
    WHERE refresh_type IN (1,2)
FOR READ ONLY
do
    EXECUTE IMMEDIATE (string('refresh text index [', index_name, '] on [', user_name, '].[', TABLE_NAME, '] force build'));
END FOR 
END
}