Get file size

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)
Intro to Tally Tables and Functions

alex_toronto27 SSC Enthusiast Points: 106 More actions · Answer 4

alex_toronto27

SSC Enthusiast

Points: 106

February 14, 2011 at 7:45 am

#1286488

Thanks, Jeff

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

You bet, Alex... thanks for the feedback.

--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)
Intro to Tally Tables and Functions

hai21century Old Hand Points: 397 More actions · Answer 6

hai21century

Old Hand

Points: 397

December 20, 2011 at 4:46 pm

#1423957

Jeff,

I love your script.

Thanks

Hai

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Thanks, Hai.

--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)
Intro to Tally Tables and Functions

onlygoodones SSC Eights! Points: 870 More actions · Answer 8

onlygoodones

SSC Eights!

Points: 870

June 23, 2013 at 8:56 am

#1626611

Thanks a lot Jeff. Really helpful and you saved 2 days of my time ..

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

onlygoodones (6/23/2013)

Thanks a lot Jeff. Really helpful and you saved 2 days of my time ..

Thank you for the feedback. It's good to know that some of the older posts still help folks.

The proc works but, because of the multiple procedure calls in the While Loops, it's slow on directories that contain a lot of files. If you do have a whole lot of files, let me know what it is in the directory that you're looking for and we might be able to come up with a faster method.

--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)
Intro to Tally Tables and Functions

SAinCA SSCrazy Points: 2162 More actions · Answer 10

SAinCA

SSCrazy

Points: 2162

March 31, 2015 at 7:27 pm

#1787898

This saved the day for a SQL2012 need.

Many thanks!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

SAinCA (3/31/2015)

This saved the day for a SQL2012 need.

Many thanks!

Wow. Even after all this time. Thank you for the feedback. Care to share any details on what you're doing? I'm always curious.

--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)
Intro to Tally Tables and Functions

SAinCA SSCrazy Points: 2162 More actions · Answer 12

We sadly lost our original customer and the loss seems irrevocable. Thus, why leave their millions of records in our 10Bn row raw-data table?

So I'm building a somewhat intelligent archival tool, based around bcp as we already use it successfully at our remote SQL Express locations.

The tenet, "First, do no harm" applies here: don't delete anything until you're sure you have a non-zero KB length file in place!

So, having looked at the old xp_cmdshell method and being too lazy to work out if the sp_OA% procedures might help, off to Google-land I trod.

It took an hour or so of looking at other solutions, refining searches, etc., but I came across your SP late yesterday, snagged the code, topped-it with my DBA DB USE, etc., and was delighted to see it compile successfully first time, then run faultlessly to list out SQL Agent Job log files, as a proof of use.

Taking your CREATE TABLE #FileDetails and making it my DECLARE @FileDetails TABLE was an easy clone, and here I am, WHERE EXISTS Path = @mypathandfile AND Size > 0, later, with a solution that fits my exact need.

You have my (continued) admiration for the lengths you must have gone to in order to put this thing together. Beyond that, making it available free gratis and for nowt is highly praiseworthy.

Again, MANY thanks, Jeff.