List all files in directory

[dky1e]

Hi,

I am aware of master..xp_dirtree.

How would I retrieve a list of all files in a folder?

I've been playing around with Scripting.FileSystemObject Folder Object which returns a collection of Object File. Unfortunately I don't know how to get at each of the File(s) to retrieve Name Property.


FYI: here's the script using Scripting.FileSystemObject:

declare  @hr		int
	,@fileSystem	int
	,@folder	int
	,@files		int
	,@filesCount 	int
	,@object 	int
	,@file		int
	,@filename	varchar(255)


exec @hr = sp_OACreate 'Scripting.FileSystemObject', @fileSystem out
print @hr
if @hr <> 0 begin set @object = @fileSystem goto error end

exec @hr = sp_OAMethod @fileSystem, 'GetFolder', @folder out, 'c:\test'
print @hr
if @hr <> 0 begin set @object = @fileSystem goto error end

exec @hr = sp_OAGetProperty @folder, 'Files', @file out
print @hr
if @hr <> 0 begin set @object = @folder goto error end

--now I have a collection of files, now how do I get at each of the items in the collection?



goto ext
error:
exec sp_OAGetErrorInfo @object

ext:

exec @hr = sp_OADestroy @fileSystem

 

[mrdenny]

Use xp_CMDShell to load the file list into a table then process it as needed.

create Table #Test1
(FileName varchar(100))

insert into #Test1
exec xp_CMDShell 'dir c:\Path\To\View /B'

select *
from #Test1

drop table #Test1

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[dky1e]

I like to avoid shelling out to cmd, but I love simple solutions

 

[mrdenny]

I agree, shelling out to cmd should be done rarly, but there are times when it's just the easiest way to do things.

Denny

--Anything is possible. All it takes is a little research. (Me)