SQL Server 2017: Less xp_cmdshell?


SQL Server 2017 RC1 dropping recently reminded me of a couple things I wanted to blog about finding in there. One that I thought was rather interesting is a new iTVF called dm_os_enumerate_filesystem. It looks like a partial replacement for xp_cmdshell in that, well, you can do the equivalent of running a dir command, with some filtering.

The addition of a search filter is particularly nice, since the dir command isn’t exactly robust in that area. If you’ve ever wanted to filter on a date, well… That’s probably when PowerShell got invented.

If I run a simple call to the new function like so…

I get this back:

This can be particularly useful for backup tasks, or folder watching tasks. For instance, I can look in a backup directory for recent backups with a size greater than 0 bytes that haven’t been written to in the last 30 seconds (a pretty good sign that a backup is completed, no?)

And that’s a heck of a neck easier than what we have to do, currently.

Thanks for reading!

Previous Post
ColumnStore Indexes And Recursive CTEs
Next Post
Live Blogging: Erik vs. PowerShell

14 Comments. Leave new

  • Corey Gilson
    July 28, 2017 11:18 am

    Coolness! Almost as cool as “CREATE OR ALTER”

  • Wonder if this’ll get backported to older versions? If they’re putting DBCC CLONEDATABASE in 2012 SP4, there’s hope for this, right? A guy can dream…

  • Alex Friedman
    July 30, 2017 6:24 am

    Oooh shiny

  • A lot better that good old undocumented master.sys.xp_dirtree

  • It’s about damned time that MS did this. Now, if they would only make BULK EXPORT and some tools for file handling, we’d have it made. It would also be really cool if they built some functionality that would actually handle things like real CSV and TAB delimited functionality.

  • p.s. And, yeah… it’s a real shame they’re not back porting this to 2016.

  • this website is my inspiration , real good style and perfect articles.