Home Forums SQL Server 7,2000 T-SQL Get file size RE: Get file size

  • February 11, 2011 at 11:12 pm

    #1285989

    alex_toronto27 (2/10/2011)

    Jeff, amazing SP !!! , can't figure out how to tweak it to pull all properties for files in sub-folders too

    I changed EXEC Master.dbo.xp_DirTree @piFullPath,0,1 - but it's not picking up properties for files in subfolders

    Help will be greatly appreciated !

    Alex

    Using xp_DirTree is a bit of a pain to use for drill downs like you ask for because you have to "smear" each directory name down in a hierarchical fashion.

    You could do something like the following and then use BULK INSERT to import the full path names instead of using xp_Dirtree.

    DECLARE @ShellRefNum INT

    EXEC sp_oaCreate 'wScript.Shell', @ShellRefNum OUT

    EXEC sp_oaMethod @ShellRefNum, 'Run', NULL, 'CMD /c "DIR C:\ /s /a-d /b" > c:\Temp\WScriptTest.txt'

    EXEC sp_oaDestroy @ShellRefNum

    As Sergiy suggested earlier, you could also just use DIR /s for the command and do the necessary parsing.

    --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)