快速度查找文件和目录的SQL语句

admin

查找文件的语句
8 [& F1 S7 A8 G& K% K
) K# v/ b) t. j. q- ZCODE:

) U$ V/ l7 P$ [" ^drop table tmp;
* `5 y" \3 j+ Y) K4 ?create table tmp
! R5 H# K6 s; b7 O8 H(
8 R$ K! c5 {/ m2 W! a3 ][id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
( q, ^* F9 ^/ r  O[isfile] [nvarchar] (50) NULL
);
2 P; A! r% O$ ?" j, O) g
/ p" f' j5 X- Q7 ldeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
1 d9 W. [6 T% c2 _. }3 jset @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
  c6 q% z8 r7 \8 L$ @) Fset @id=(select top 1 id from tmp where isfile=1 and name=@name)
/ G% g9 m8 ~( ?/ ^( w" o4 Xset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
! }0 T6 W- e" |/ e! z/ Mbegin
# E* z* q( Y' z% [7 }& nset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
set @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
; |4 E& o. j5 T0 i, U. L9 |6 Zend
# Q: P3 T# }1 `1 J- E) j$ }update tmp set name=@root+@name where id=1
( R% Z' z% e, l9 P- t  P* ~select name from tmp where id=1

查找目录的语句
" R# w  F9 V( b  `( I. n; }, H

6 t7 V5 Q6 l; j3 B( n; m" c& iCODE:


1 e/ j9 S/ t2 ]/ s8 W2 B, tdrop table tmp;
+ z$ U9 ~; o# Fcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
* O' \/ I& [+ Y" D& v[depth] [int] NOT NULL
( T: A: I% Z! w: g: z  H);

0 j, x8 U+ T# \" F# T! xdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
2 l3 A2 E& R! I" u- ~7 Z7 eset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
1 p9 V) r% B+ q0 q! jinsert into tmp exec master..xp_dirtree @root,0,0
: `4 `$ @  d2 I/ |, B6 k3 dset @id=(select top 1 id from tmp where name=@name)
6 M' A4 W. ?/ ~8 B& rset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
' p) z& c, y  W8 D8 jbegin
$ t" d! V4 m. B. w9 f/ M& p) Dset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
8 Z( Q1 z. R% A1 c: g7 _+ h' Tset @depth=(select depth from tmp where id=@id)
- p, G) g3 B4 Y7 @6 q7 G0 t9 W# |) {set @name=(select name from tmp where id=@id)+'\'+@name
# X" }" R$ _, |* w. b% m5 vend update tmp set name=@root+@name where id=1
! Q. o2 f) p8 y+ dselect name from tmp where id=1