File Size from T-SQL

Question

File Size from T-SQL

Viewing 5 posts - 16 through 19 (of 19 total)

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

In Powershell it should be something like this - just change 'c:\' to the top folder you want to query.

Set-Location 'c:\'

Get-ChildItem -Recurse | Where-Object {$_.PSIsContainer -eq $false} | Select-Object Name, Directory, @{n="Size"; e={$_.Length}}, CreationTime, LastWriteTime | Format-Table

You can see if this is any quicker.

However with that many directories & files, I can't see any solution being particularly quick.

ralu_k_17 SSCrazy Points: 2190 More actions · Answer 2

I don't have a lot of knowledge of Powershell.

Is it possible to query a path in another server?

And also, to insert the results into a certain table?

Thank you!

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

Yep, just use a UNC path - \\servername\... instead of the local path.

It's a bit more work to insert into a SQL table but thought you could do a speed test first to see if it's feasible performance-wise.

There's an article on this site which will help, it's actually very close to what you're doing so may not need much modification:

http://www.sqlservercentral.com/blogs/nycnet/2013/05/24/performing-an-insert-from-a-powershell-script/

That script inserts row by row, but I'd recommend trying the insert row-by-row and all at once to see if there's a performance difference.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 4

Gazareth (10/20/2014)
Yep, just use a UNC path - \\servername\... instead of the local path.
It's a bit more work to insert into a SQL table but thought you could do a speed test first to see if it's feasible performance-wise.
There's an article on this site which will help, it's actually very close to what you're doing so may not need much modification:
http://www.sqlservercentral.com/blogs/nycnet/2013/05/24/performing-an-insert-from-a-powershell-script/
That script inserts row by row, but I'd recommend trying the insert row-by-row and all at once to see if there's a performance difference.

Heh... I'm definitely a neophyte when it comes to PoSH. After all this time looking, someone finally came up with a good way to do this using PoSH. The neat thing is, it can all be done auto-magically by calling PoSH from xp_CmdShell. I can't speak for the OP but this certainly will satisfy my requirements. Thank you for your example code and the link.

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 5

Jeff Moden (10/20/2014)
Gazareth (10/20/2014)
Yep, just use a UNC path - \\servername\... instead of the local path.
It's a bit more work to insert into a SQL table but thought you could do a speed test first to see if it's feasible performance-wise.
There's an article on this site which will help, it's actually very close to what you're doing so may not need much modification:
http://www.sqlservercentral.com/blogs/nycnet/2013/05/24/performing-an-insert-from-a-powershell-script/
That script inserts row by row, but I'd recommend trying the insert row-by-row and all at once to see if there's a performance difference.
Heh... I'm definitely a neophyte when it comes to PoSH. After all this time looking, someone finally came up with a good way to do this using PoSH. The neat thing is, it can all be done auto-magically by calling PoSH from xp_CmdShell. I can't speak for the OP but this certainly will satisfy my requirements. Thank you for your example code and the link.

You're very welcome Jeff!