Recently I published a blog about new DMV in SQL Server 2017 called dm_os_enumerate_fixed_drives. While doing that I found another new DMF (Dynamic Management Function) sys.dm_os_file_exists.
Another new DMF which I saw in SQL Server 2017 was sys.dm_os_file_exists. I was thinking of a reason to have a new function, but later I realized that same was available as an extended procedure called xp_fileexist. I think Microsoft is changing it because such file / OS level extended procedure may not behave same in Windows and Linux.
Here is the usage of old version
EXEC xp_fileexist 'c:\Windows\notepad.exe';
New DMF also has same usage and output.
SELECT * FROM sys.dm_os_file_exists('c:\Windows\notepad.exe');Here is the screenshot
As we can see above, the output is the same format. Please note that sys.dm_os_file_exists will not work with the earlier version of SQL Server and only supported after SQL Server 2017.
Do you use a similar feature in your business. Please leave a comment and let me know what is the reason you have to use this procedure. Just like me everyone would be interested to know. Sharing is the best way to learn new features.
Here is my another blog post which may interest you:
System Stored Procedures I Use to Get Started
Reference: Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
It seems that like its predecessor stored procedure xp_fileexists, the function sys.dm_os_file_exists is undocumented, and therefore its not quite right to say it will be “supported” after SQL Server 2017.
Ooooooooo this is nice! Thanks Pinal, this just came in SUPER handy.
i’ve tested both options in SQL server 2017 on Linux using SET STATISTICS TIME, IO ON.
both works as expected and returns same result for local as well as mapped network paths.
the only difference i’ve found is that for mapped network paths – the new DMF (sys.dm_os_file_exists) takes longer time (~30%) to return result than xp_fileexist.