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

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

  • Doesn’t seems to work for UNC/network paths. The folder path needs to be mapped to a drive letter.

    • Haven’t tried a UNC yet but it’s so typical of MS to take a great idea and cripple it. Fortunately, xp_CmdShell still works a treat.

      And, all you folks getting ready to stand up and yell about it being a security risk? Please sit back down. If you know how to configure things properly, there’s no risk even if you leave it on all the time.


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.