Post reply

File List ??

  • February 12, 2004 at 7:10 pm

    #107076

    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.

     

    --------------------
    Colt 45 - the original point and click interface

  • February 12, 2004 at 8:45 pm

    #493927

    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

  • February 12, 2004 at 8:55 pm

    #493930

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

     

    --------------------
    Colt 45 - the original point and click interface

  • February 12, 2004 at 9:01 pm

    #493931

    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

  • October 13, 2011 at 9:48 pm

    #1394437

    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.

    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)

  • June 6, 2013 at 12:42 pm

    #1621874

    good work

    🙂

Viewing 6 posts - 1 through 5 (of 5 total)

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