How to create update statistics script

/* update_statistics.p
* Dmitri Levin with thanks to Darrell Davis and Tom Bascom
* 10/17/2011
*/
define stream tstr.
define stream istr.
define stream cstr.
define variable count_index as integer no-undo.

output stream tstr to "UpdTblStats.sql".
output stream istr to "UpdIdxStats.sql".
output stream cstr to "UpdColStats.sql".

for each _File no-lock where _Tbl-Type = "T":

  put stream tstr unformatted "update table statistics for PUB."
     quoter(_file-name) ";" skip.
  put stream tstr unformatted "commit work;" skip.
  put stream tstr unformatted "select top 1 '" +
    _file-name + "' as 'table',curtime() from PUB.""_file"";" skip.

  count_index = 0.
  for each _index of _file no-lock:
    count_index = count_index + 1.
  end.
  if count_index >= 4 then do:
    put stream istr unformatted "update index statistics for PUB."
       quoter(_file-name) ";" skip.
    put stream istr unformatted "commit work;" skip.
    put stream istr unformatted "select top 1 '" +
    _file-name + "' as 'table',curtime() from PUB.""_file"";" skip.
  end.

  put stream cstr unformatted "update column statistics for PUB."
    quoter(_file-name) ";" skip.
  put stream cstr unformatted "commit work;" skip.
  put stream cstr unformatted "select top 1 '" +
    _file-name + "' as 'table',curtime() from PUB.""_file"";" skip.
end.
output stream tStr close.
output stream iStr close.
output stream cStr close.


Return to myProgress Home Page
    

    

    

    

    

    

    

1