File Size from T-SQL

  • 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.

  • 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!

  • 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.

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

  • 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!

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

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