SQL Server find filename from directory column

env:Windows Server 2016
       SQL Server 2016 SP2

column裡面儲存的資料是檔案名稱與路徑，要怎麼順利把檔案名稱取出來?

1.建立存放測試資料的表
command:
CREATE TABLE #DirTree
(
subdirectory NVARCHAR(200),
depth INT,
isfile BIT
)

2.利用xp_dirtree放入資料到測試表
command:
INSERT INTO #DirTree (subdirectory, depth, isfile)
EXECUTE master.sys.xp_dirtree ' F:\DBBackup\dbserver03\Fullbackup\',1,1

3.再利用測試表的資料做出含有路徑檔名的完整測試資料
command:
SELECT 'F:\DBBackup\dbserver03\Fullbackup\' + subdirectory AS FullDirectory
INTO #DirTreeFull
FROM #DirTree

4.最後利用REVERSE與CHARINDEX這個function將檔名分離到一個獨立欄位
command:
SELECT *,
REVERSE(left(REVERSE(FullDirectory), CHARINDEX('\', REVERSE(FullDirectory)) - 1)) AS [FileName]
FROM #DirTreeFull

5.下面語法分離路徑與檔案
command:
SELECT *,
REVERSE(SUBSTRING(REVERSE(FullDirectory), CHARINDEX('\', REVERSE(FullDirectory)) + 1, LEN(FullDirectory))) AS [DirectoryName],
REVERSE(left(REVERSE(FullDirectory), CHARINDEX('\', REVERSE(FullDirectory)) - 1)) AS [FileName]
FROM #DirTreeFull