Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

File List ?? Expand / Collapse
Author
Message
Posted Thursday, February 12, 2004 7:10 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:41 PM
Points: 2,693, Visits: 1,203

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
Post #100177
Posted Thursday, February 12, 2004 8:45 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 12:42 AM
Points: 5,135, Visits: 386

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
Post #100186
Posted Thursday, February 12, 2004 8:55 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:41 PM
Points: 2,693, Visits: 1,203

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
Post #100188
Posted Thursday, February 12, 2004 9:01 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 12:42 AM
Points: 5,135, Visits: 386

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
Post #100189
Posted Thursday, October 13, 2011 9:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 36,938, Visits: 31,441
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1190292
Posted Thursday, June 6, 2013 12:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 10:29 AM
Points: 1, Visits: 51
good work
Post #1460844
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse