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

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.