Pokin’
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…
|
1 2 |
SELECT * FROM sys.dm_os_enumerate_filesystem('C:\', '*') |
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?)
|
1 2 3 4 5 |
SELECT * FROM sys.dm_os_enumerate_filesystem('C:\Backups\', '*.bak') WHERE creation_time >= GETDATE() AND last_access_time <= DATEADD(SECOND, -30, last_write_time) AND size_in_bytes > 0 |
And that’s a heck of a neck easier than what we have to do, currently.
Thanks for reading!
22 Comments. Leave new
Coolness! Almost as cool as “CREATE OR ALTER”
What about DROP IF EXISTS?
Now it has a coolness complex.
Look what you’ve done.
Drop If Exists earns extra coolness points for having a perfect acronym.
A+
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…
I hope it doesn’t, so you all have to upgrade 🙂
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.
Jeff — yeah, that’d be nice, but you’ll always have folks yelling at you to “just use SSIS” for stuff like that.
Heh… I hate SSIS.
I’ve still never opened it 🙂
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.
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.
Heh… haven’t had urgent reason to use this lately because all the old stuff using xp_CmdShell, etc, still works.
I’ve lately had reason to use the new sys.dm_os_enumerate_filesystem function and thought I’d provide a little warning if you’ve not already noticed…
The dates and times it returns are NOT the dates and times stored in the file system that you get from a DIR. Instead, they’re the UTC dates and times.
HAHAHA, that’s awesome. What a weird surprise.