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
