/*
EXEC master.dbo.xp_cmdshell 'copy c:\a.txt d:\log\' --- Copy EXEC master.dbo.xp_subdirs 'd:\' --- 显示指定目录下有哪些文件夹
EXEC master.dbo.xp_dirtree 'd:\', @depth = 1 -- 显示指定目录有哪些子目录 (@depth = 1 表示只遍历深度为 1 的目录;此变量不填写,就遍历所有子目录) EXEC master.dbo.xp_cmdshell 'dir /b/s d:'
*/
DECLARE @Path nvarchar(260) SET @Path = 'C:\Downloads' IF RIGHT(@Path, 1) <> '\' SET @Path = @Path + '\' IF OBJECT_ID('tempdb..#File') IS NOT NULL DROP TABLE #File CREATE TABLE #File( id int IDENTITY, --编号 directory nvarchar(260), --路径 depth int, --深度,相对与@path IsFile bit )--0文件夹1文件名成 INSERT #File EXEC master.dbo.xp_dirtree @path = @path, @depth = 0, @file = 1 DECLARE @depth int, @depthMax int UPDATE #File SET directory = @Path + directory WHERE depth = 1 SELECT @depth = 2, @depthMax = MAX(depth) FROM #File WHILE @depth <= @depthMax BEGIN UPDATE A SET directory = ( SELECT TOP 1 directory FROM #File WHERE depth = @depth - 1 AND IsFile = 0 AND id < A.id ORDER BY id DESC ) + N'\' + directory FROM #File A WHERE depth = @depth SET @depth= @depth + 1 END SELECT * FROM #File