Parse File Name and Parent Directory From Full File System Path

A SQL Server question I've seen posed frequently is how to parse a full file system path, splitting it to obtain either the file name or the parent directory or both. Most solutions I’ve seen involve string functions like REVERSE(), LEFT(), RIGHT(), CHARINDEX(), etc. Some have even resorted to STRING_SPLIT(), STRING_AGG(), or PARSENAME().

Most of those solutions have some edge cases that cause failures: full file system paths with more than four . characters, files that don’t have an extension, etc. The really robust solutions that never fail often have logic that is a little difficult to follow, or are difficult to implement without resorting to use of a user-defined function.

I’d like to share my version, which I find simple and elegant. But there are a couple of caveats. More on that in a bit...

Let’s use this file as an example: D:\MSSQL16.MSSQLSERVER\MSSQL\Log\error_reported_0_134260164193660000.xel
It’s a file created by an Extended Events session on the D drive of a SQL Server host. I’ll create a TSQL variable for the full file system path string and pass it to dynamic management function sys.dm_os_enumerate_filesystem:

DECLARE @full_filesystem_path NVARCHAR(512) = 'D:\MSSQL16.MSSQLSERVER\MSSQL\Log\error_reported_0_134260164193660000.xel'

SELECT fs.parent_directory
	,fs.file_or_directory_name
FROM sys.dm_os_enumerate_filesystem(@full_filesystem_path + '\..', '*.*') AS fs
WHERE fs.full_filesystem_path = @full_filesystem_path

Below is the output showing the parsed parent directory and the file name (with extension).

If you noticed, you might be asking “What’s that \.. appended to the @full_filesystem_path variable? Do you remember your DOS commands? .. takes you “up” one level in the file system. Here’s an example using the CD (change directory) command:

In the TSQL code, @full_filesystem_path + '\..' tells Windows to go to the parent folder of the file. The second parameter passed to sys.dm_os_enumerate_filesystem is ‘*.*’, which is the wildcard character ‘*’ for both the file name and the file extension. If no WHERE clause is used, the result set returned will show all folders and files in the path. The WHERE clause in this demo narrows down the results to just our file: WHERE fs.full_filesystem_path = @full_filesystem_path

This approach seems to work with those edge cases I mentioned above. Here’s an example using the most recent SQL Server error log file, which does not have a file extension:

DECLARE @full_filesystem_path NVARCHAR(512) = 'D:\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG'

SELECT fs.parent_directory
	,fs.file_or_directory_name
FROM sys.dm_os_enumerate_filesystem(@full_filesystem_path + '\..', '*.*') AS fs
WHERE fs.full_filesystem_path = @full_filesystem_path

Gotchas

OK, now for those caveats I mentioned. Firstly, the file has to exist in a path the SQL Server host can access. If you’re working with strings to arbitrary files that don’t exist relative to SQL Server, this solution is not for you. Secondly, using SQL Server for file system operations isn’t really what a relational database management system is for. But we already knew that, right?