pears.DeleteOldDatabaseLogs

CREATE FUNCTION "pears"."DeleteOldDatabaseLogs"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN @DaysToKeep SMALLINT ) 
RETURNS SMALLINT
BEGIN
  --Deletes all files log files with pattern 000000AA.LOG in current log folder over a given age. Read only flags are ignored.
  DECLARE @Command CHAR(250);
  DECLARE @COUNT SMALLINT;
  DECLARE @LogFilePathName CHAR(250);
  DECLARE @LogPath CHAR(250);
  SET @LogFilePathName = "db_property"('LogName');
  SET @LogPath = "left"(@LogFilePathName,"locate"(@LogFilePathName,'\\',-1)-1);
  SET @COUNT = 0;
  FOR "DeleteLoop" AS "DeleteCursor" no scroll cursor FOR
    SELECT "file_path" AS "DeleteLogFilePathName"
      FROM "sp_list_directory"(@LogPath)
      WHERE "file_type" = 'F'
      AND "upper"("right"("file_path","length"("file_path")-"locate"("file_path",'\\',-1))) REGEXP '^[[:digit:]]{6}[[:alnum:]]{2}\\.LOG$'
      AND "modified_date_time" < "dateadd"("day",(@DaysToKeep*-1),CURRENT DATE)
      AND "file_path" <> @LogFilePathName FOR READ ONLY
  do
    SET @Command = "string"('DEL /F "',"DeleteLogFilePathName",'"');
    CALL "xp_cmdshell"(@Command,'no_output)');
    SET @COUNT = @COUNT+1
  END FOR;
  RETURN @COUNT
END