--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
Nice one, Nick.
The recursive CTE is orders of magnitude quicker than the other solutions that are using cursors and calling xp_dirtree thousands of times. Hours of hammering the file system down to a single call.
Kev
P.S. You don’t need to enable xp_cmdshell to use xp_dirtree 🙂