Technical Article

Populate a table with a directory's file info.

,

I use this SP to return full directory information into a table.  FilePath, FileName, Filedate and FileSize are parsed from a 'dir /n/on/-c ' command.
FilesInDir is the table created.
usage:
exec LoadFileDetailsIntoTable 'c:\winnt'
select * from FilesInDir where filesize > 100000
select min(fileDate) from FilesInDir
The Return Code will equal the number of files found.

create  Proc LoadFileDetailsIntoTable @filepath as varchar(255)
as 
if not exists (select * from dbo.sysobjects where id = object_id(N'[FilesInDir]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
CREATE TABLE [FilesInDir] 
(
[InsertDateTime] [datetime] NULL ,
[Filepath] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Filename] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Filedate] [datetime] NULL ,
[Filesize] [int] NULL 
) 
end

Truncate table FilesInDir

if not exists (select * from dbo.sysobjects where id = object_id(N'[dirTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
CREATE TABLE [dirTable] 
(
[DirEntry] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) 
end

Truncate table dirTable

declare @cmd varchar(200)
set @cmd = 'dir ' + @filepath + ' /n/on/-c ' 

insert into dirTable exec  Master..xp_cmdshell @cmd 

insert into FilesInDir
select 
getdate() as InsertDate,
@filePath as FilePath,
substring(direntry,40,255) as FileName,
dateadd
(hh,
case 
when 
charindex('p',left(direntry,20))>0 
then 
12 
else 
0 
end,
convert(datetime,left(rtrim(left(direntry,20)),len(rtrim(left(direntry,20)))-1)))
as FileDate,
convert(int,substring(direntry,24,15)) as FileSize
from 
dirtable
where 
substring(direntry,3,1) + substring(direntry,6,1) ='//'
and charindex('<dir>',direntry,1) =0

return @@rowcount

Rate

Share

Share

Rate