SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


File List ??


File List ??

Author
Message
philcart
philcart
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9669 Visits: 1441

Hi All

I'm building a process that reads a directory from the disk and stores the path and files details in a table.

Does anyone know of a xp (documented or undocumented) that will list all files in a given directory? I know I can use xp_cmdshell and do a DIR, but I'd prefer not to do it this way as end-users will be running the process.

I can get the directories easy enough using xp_dirtree, but I can't seem to find anything that'll list the files.

If there isn't one that lists files I'll just have to hack together a VBScript.



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
mccork
mccork
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7196 Visits: 444

Phill,

xp_dirtree combined with xp_getfiledetails may do the job for you.

eg. EXECUTE master.dbo.xp_dirtree N'C:\TEMP\', 1, 1

one of the columns returned will say whether the entry is a subdirectory or a file.

To get file details:

CREATE TABLE #FileInfo (
alt_name varchar(255) null,
size_in_bytes int null,
creation_date int null,
creation_time int null,
last_written_date int null,
last_written_time int null,
last_accessed_date int null,
last_accessed_time int null,
attributes int null
)

INSERT #FileInfo EXEC master.dbo.xp_getfiledetails @Filename




Cheers,
- Mark
philcart
philcart
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9669 Visits: 1441

Ah Ha ... where did you find out about the parameters for xp_dirtree?



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
mccork
mccork
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7196 Visits: 444

well... um... er... I don't really know what those parms do. I guess the first says the depth you want to go down to. And the second, if non-null and non-zero, says that you want files as well as dirs.

I knew EM returned a list of files (when you choose an output file for a scheduled job step), so I just used profiler to capture what it was doing.




Cheers,
- Mark
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213105 Visits: 41977
I know it's an old post but right, back in SAQL Server 2000, if you right clicked on an extended sproc and told it to execute, the number of parameters and their names would appear. That no longer works in 2k5 and above.

Most folks know that the second operand of xp_DirTree would limit the number of levels but most folks don't know that the third operand will return file names anytime the third operand is a non-zero value. Most of this was discovered just by trial and error.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ronald.riviere 31618
ronald.riviere 31618
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 145
good work
:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search