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

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

  • Philippe Addelia
    March 9, 2022 10:42 pm

    My all time favorite is STRING_AGG, followed closely by LAG.
    No more annoying For_XML path stuff.
    I am stuck working with SQL 2016 SP2 database at work. too bad, I cannot use coding candy.
    Upgrade to SQL 2019 is still a couple years down the road (today is Mar 22).

  • Just tried using it . It runs fine on by D: drive but, when I run it on my C: drive, I get the following exciting message. Since it’s not a supported feature, there’s not much that can be done for such problems.

    Msg 407, Level 16, State 1, Line 1
    Internal error. The string routine in file sql\ntdbms\storeng\dfs\alloc\storagedmv.cpp, line 799 failed with HRESULT 0x8007007a.

  • Does this only work on a local machine? I have been unsuccessful to a remote machine with something like this:

    select * from sys.dm_os_enumerate_filesystem(‘\\smuc13652.bmwgroup.net\E$’,’*.*’)

    • My guess is no, and I wouldn’t have even thought to try it, heh. Once you start jumping to other servers, that’s not really the filesystem – it’s a network call.


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.