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