How to do a complete path directory listing with transact sql, xp_dirtree and a recursive common table expression

--How to Use xp_dirtree to List All Files in a Folder http://www.patrickkeisler.com/2012/11/how-to-use-xpdirtree-to-list-all-files.html
--Recursive Directory Listing in Transact-SQL: https://www.brad-smith.info/blog/archives/70
--enable extended procs http://www.databasejournal.com/features/mssql/xpcmdshell-for-non-system-admin-individuals.html

--enable extended procs (if you have permission! if not, you're stuffed)
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE



IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
 DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (
 fileId int IDENTITY(1,1)
 ,fullPath nvarchar(2000)
 ,subdirectory nvarchar(max)
 ,depth int
 ,isfile bit);

INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree 'C:\Projects\MyStaffFile2\SomeTestData',0,1;

;with fileparents(fileid, subdirectory, parentid, isfile) as (
 select c.fileid, c.subdirectory, (select max(p.fileId) from #DirectoryTree p where p.depth=c.depth-1 and p.fileId<c.fileId) parentId , c.isfile
 from #DirectoryTree c
),DirListing (fileid,subdirectory, isfile) as(
 select fileid, subdirectory + '' subdirectory, isfile from fileparents where parentid is null
 union all
 select f.fileid, d.subdirectory +'\' + f.subdirectory subdirectory, f.isfile
 from DirListing d join fileparents f on f.parentid=d.fileid
)
select l.subdirectory, 'file not in documents table' from DirListing l
left outer join Documents d on l.subdirectory=d.DocumentPath where d.DocumentId is null
--do something useful with this listing

--turn extended procs back off
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE