Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


File List ??


File List ??

Author
Message
philcart
philcart
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2748 Visits: 1434

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 (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5464 Visits: 440

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
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2748 Visits: 1434

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 (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5464 Visits: 440

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-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44871 Visits: 39850
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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: 142
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