· 7 years ago · Nov 14, 2018, 07:50 PM
1DECLARE @dir NVARCHAR(260) ;
2SELECT @dir = N'c:temp' ;
3
4IF RIGHT(@dir, 1) <> ''
5 SELECT @dir = @dir + '' ;
6
7IF OBJECT_ID('tempdb..#dirtree', 'U') IS NOT NULL
8 DROP TABLE #dirtree ;
9CREATE TABLE #dirtree
10(
11 id INT PRIMARY KEY
12 IDENTITY,
13 subdirectory NVARCHAR(260),
14 depth INT,
15 is_file BIT
16) ;
17
18INSERT INTO #dirtree
19 EXEC xp_dirtree
20 @dir,
21 0,
22 1 ;
23
24SELECT *
25FROM #dirtree ;
26
27WITH files
28 AS (
29 SELECT id,
30 subdirectory,
31 depth,
32 is_file, subdirectory AS path
33 FROM #dirtree
34 WHERE is_file = 1
35 AND depth <> 1
36 UNION ALL
37 -- ...
38 )
39 SELECT *
40 FROM files ;
41
42/*
43id subdirectory depth is_file
44--- -------------- ------- -------
451 abc.mdf 1 1
462 a 1 0
473 a.txt 2 1
484 b.txt 2 1
495 a.rb 1 1
506 aaa.flv 1 1
51*/
52
53/*
54path
55------------------
56c:tempabc.mdf
57c:tempaa.txt
58c:tempab.txt
59c:tempa.rb
60c:tempaaa.flv
61*/
62
63CREATE TABLE #dirtree
64(
65 id INT,
66 subdirectory NVARCHAR(260),
67 depth INT ,
68 is_file BIT,
69 parentId INT
70)
71
72INSERT INTO #dirtree(id,subdirectory,depth,is_file)
73VALUES
74 (1,'abc.mdf',1,1),(2,'a',1,0),(3,'a.txt',2,1),
75 (4,'b.txt',2,1),(5,'a.rb',1,1),(6,'aaa.flv',1,1)
76
77UPDATE #dirtree
78SET ParentId = (SELECT MAX(Id) FROM #dirtree
79 WHERE Depth = T1.Depth - 1 AND Id < T1.Id)
80FROM #dirtree T1
81
82;WITH CTE
83AS
84(
85 SELECT
86 t.id,
87 t.subdirectory,
88 t.depth,
89 t.is_file
90 FROM
91 #dirtree AS t
92 WHERE
93 is_file=0
94 UNION ALL
95 SELECT
96 t.id,
97 CAST(CTE.subdirectory+''+t.subdirectory AS NVARCHAR(260)),
98 t.depth,
99 t.is_file
100 FROM
101 #dirtree AS t
102 JOIN CTE
103 ON CTE.id=t.parentId
104 )
105SELECT
106 'c:temp'+CTE.subdirectory AS [path]
107FROM
108 CTE
109WHERE
110 CTE.is_file=1
111UNION ALL
112SELECT
113 'c:temp'+t.subdirectory
114FROM
115 #dirtree AS t
116WHERE
117 is_file=1
118 AND NOT EXISTS
119 (
120 SELECT
121 NULL
122 FROM
123 CTE
124 WHERE
125 CTE.id=t.id
126 )
127
128path
129---------------
130c:tempaa.txt
131c:tempab.txt
132c:tempabc.mdf
133c:tempa.rb
134c:tempaaa.flv
135
136/*
137 traverse directory tree and get back complete list of filenames w/ their paths
138*/
139
140declare
141 @dirRoot varchar(255)='\yourdir'
142
143declare
144 @sqlCmd varchar(255),
145 @idx int,
146 @dirSearch varchar(255)
147
148declare @directories table(directoryName varchar(255), depth int, isfile int, rootName varchar(255),rowid int identity(1,1))
149
150insert into @directories(directoryName, depth,isFile)
151exec master.sys.xp_dirtree @dirRoot,1,1
152
153if not exists(select * from @directories)
154 return
155
156update @directories
157set rootName = @dirRoot + '' + directoryName
158
159-- traverse from root directory
160select @idx=min(rowId) from @directories
161
162-- forever always ends too soon
163while 1=1
164begin
165
166 select @dirSearch = rootName
167 from @directories
168 where rowid=@idx
169
170 insert into @directories(directoryName, depth,isfile)
171 exec master.sys.xp_dirtree @dirSearch,1,1
172
173 update @directories
174 set rootName = @dirSearch + '' + directoryName
175 where rootName is null
176
177 set @idx = @idx + 1
178
179 -- you see what i mean don't you?
180 if @idx > (select max(rowid) from @directories) or @idx is null
181 break
182
183end
184
185 select
186 case isFile when 0 then 'Directory' else 'File' end [attribute],
187 rootName [filePath]
188 from @directories
189 order by filePath
190
191create or alter proc sp_dirtree
192 @Path nvarchar(4000)
193 , @Depth int = 0
194 , @FileOnly bit = 0
195as -- Dir tree with fullpath. sergkog 2018-11-14
196 set nocount on
197 declare @Sep nchar(1) = iif(patindex('%/%',@Path) > 0,'/','') -- windows or posix
198 set @Path += iif(right(@Path,1) <> @Sep, @Sep,'')
199
200declare @dirtree table(
201 Id int identity(1,1)
202 , subdirectory nvarchar(4000) not null
203 , depth int not null
204 , is_file bit not null
205 , parentId int null
206)
207
208insert @dirtree(subdirectory, depth, is_file)
209exec xp_dirtree @Path, @Depth, 1
210
211update @dirtree
212 set ParentId = (select max(id) from @dirtree where Depth = t1.Depth - 1 and Id < t1.Id)
213 from @dirtree t1
214
215;with cte as(
216 select t.*
217 from @dirtree t
218 where is_file=0
219 union all
220 select t.id
221 , convert(nvarchar(4000), cte.subdirectory+ @Sep + t.subdirectory)
222 , t.depth
223 , t.is_file
224 , t.parentId
225 from
226 @dirtree t join cte on cte.id = t.parentId
227 )
228 select @Path + cte.subdirectory as FullPath
229 , cte.is_file as IsFile
230 from cte
231 where cte.is_file = iif(@FileOnly = 1, 1,cte.is_file)
232 union all
233 select @Path + t.subdirectory
234 , t.is_file
235 from @dirtree t
236 where
237 t.is_file = iif(@FileOnly = 1, 1,t.is_file)
238 and not exists(select null from cte
239 where cte.id=t.id
240 )
241order by FullPath, IsFile
242go