Have you ever tried to find how many large duplicate files are taking up space on your storage system?
We had the same idea and used some very easy IBExpert scripts to read all network files into a database. With about 8 million files on several NAS systems, the tools available in Microsoft Windows are not really helpful. Here you can find our solution, including sample code, using IBExperts IBEBlock scripting language and a Firebird database for storing the inventory.

First we created a table:

CREATE TABLE F (
    FN  VARCHAR(512) NOT NULL PRIMARY KEY,     --filename incl. path
    FS  BIGINT,                                                                --filesize in bytes
    FT  TIMESTAMP,                                                       --file timestamp
    P   VARCHAR(512),                                                   --path to file without filename
    N   VARCHAR(512)                                                    --filename without path
);

Then we read all file names including path, timestamp and size into the table:

  execute ibeblock
  as
  begin
    ibec_progress('Searching for files...');
    files_count = ibec_getfiles(files_list, '\\ibexpertnas\data\', '*.*',__gfRecursiveSearch + __gfFullName);

    if (files_count > 0) then
    begin
      i = 0;
      while (i <= ibec_high(files_list)) do
      begin
       try
        file_name = files_list[i];

        file_size = ibec_filesize(file_name);                              -- File size
        file_time = ibec_FileDateTime(file_name);
        n=ibec_ExtractFileName(file_name);
        p=ibec_ExtractFileDir(file_name);
        update or insert into F (FN, FS, FT,p,n) values (:File_name, :File_Size, :File_time,:p,:n) matching (fn);
       except
        ibec_Progress(i||' von '||files_count||': '||file_name||'    '||ibec_GetLastError());
       end
        i = i + 1;
        x=ibec_mod(i,1000);
        if (x=0) then
        begin
           commit;
           ibec_Progress(i||' von '||files_count);
        end
      end
      commit;
    end
  end;

Then we moved all *.iso files from any other directory to the iso directory on the NAS:

execute ibeblock
as
begin
 for
  select f.fn,f.n from f
where (f.fn like '%.iso'
    and f.fn not starting with '\\ibexpertnas\data\iso')
 into fn,n do
 begin
   if (ibec_MoveFile(fn,'\\ibexpertnas\data\iso\'||n)) then delete from f where f.fn=:fn;
   ibec_progress(fn);
 end
 commit;
end

To get a list of the largest files on your file system, including how much space they use, how often they are stored and in which directories they can be found:

select f.n,sum(f.fs),count(*),list(f.p) from f
group by 1
order by 2 desc

The following file-handling functions are available in IBEBlock:

Important: IBEBlock is a set of DDL-Data Definition Language, DML-Data Manipulation Language and other statements that are executed on the server and on the client side, and which include some specific constructions applicable only in IBExpert or the IBExpert command-line tool IBEScript (excluding the free versions of these products), independent of the database server version.

Do you need our help? Here you can find our offers for hotline or onsite help.

back to top of page
<< Change all triggers | IBEBlock | Automatic script execution >>