Create Listing of Files

Question

Post reply

Create Listing of Files

JuanBob

SSCertifiable

Points: 5059
More actions
December 23, 2005 at 11:47 am

#169945

Is there a way to create a table and populating it with a listing of filenames for a particular folder? I need to add this type of function to a stored procedure.
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 736255

December 23, 2005 at 12:29 pm

#611357

You can

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

You can use create a table and then run a "dir" or some other mechanism to get a list of files and load into the table. you'd need to then parse things out.

the easiest way is to grab a list of files using something like VBScript and the FileSystemObject and then create a file. You can then load that into sql with DTS, BCP, etc.

JuanBob SSCertifiable Points: 5059 More actions · Answer 3

Thanks for that info! I'll look into this and hopefully get something working. I appreciate it!

bwalker SSC Veteran Points: 296 More actions · Answer 4

Another possible option...

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1099668,00.html?track=NL-464&ad=520012

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 5

Try something like this...

Declare

@Dir VarChar(1024),

@Cmd VarChar(8000)

Select

@Dir='C:\WinNT',

@Cmd='Dir /A-D /B "'+@Dir+'"'

If Object_Id('tempdb.dbo.#tmp') is not Null Drop Table dbo.#Tmp

Exec

(

'Create Table dbo.#Tmp(Dir VarChar(1024) default '''+@Dir+''',FN VarChar(256))

Insert dbo.#Tmp (FN)

Exec master.dbo.xp_CmdShell '''+@Cmd+'''

Select * from dbo.#Tmp where FN is not Null'

)

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

Tracy McKibben SSC Veteran Points: 271 More actions · Answer 6

Very easy to do...

CREATE TABLE #temp (

vchFileName VARCHAR(255)

)

INSERT INTO #temp

EXEC master..xp_cmdshell 'DIR /B C:\WINDOWS'

SELECT * FROM #temp

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Just a reminder, folks... xp_CmdShell requires SA privs or a proxy login that has SA privs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)