Почти девять лет спустя, и, к сожалению, я не знаю готового решения. Так что я все еще изучаю xp_dirtree и мне нужно решение этой проблемы.
Я попробовал ответ Ариона и обнаружил, что он дает результаты. Однако с большой файловой системой, содержащей более 11 тысяч объектов, он работал очень медленно. Я видел, что это было очень медленно даже с самого начала:
UPDATE #dirtree
SET ParentId = (SELECT MAX(Id) FROM #dirtree
WHERE Depth = T1.Depth - 1 AND Id < T1.Id)
FROM #dirtree T1
Хотя это не проблема островков и пробелов, у нее есть некоторые сходства, и способ мышления в этих проблемах помог мне здесь. Код в конце — это моя хранимая процедура. В разделах есть некоторые комментарии относительно того, что делает код.
Вы бы использовали это так:
exec directory
@root = 'c:\somepath',
@depth = 3,
@outputTable = '##results';
select * from ##results;
Что приводит к выводу, например:
+---------------------------------+------------+------------+-----------+--------+-----------+----------+
| path | name | nameNoExt | extension | isFile | runtimeId | parentId |
+---------------------------------+------------+------------+-----------+--------+-----------+----------+
| c:\somePath\DataMovers | DataMovers | DataMovers | NULL | 0 | 4854 | NULL |
| c:\somePath\DataMovers\main.ps1 | main.ps1 | main | ps1 | 1 | 4859 | 4854 |
+---------------------------------+------------+------------+-----------+--------+-----------+----------+
Мне пришлось построить его таким образом, потому что внутри он берет вывод xp_dirtree и загружает его во временную таблицу. Это предотвращает возможность брать результаты процесса и загружать их в таблицу вне процесса из-за запрета на вложенные операторы insert-exec. Не раскрывайте @outputTable ненадежным пользователям, потому что он подвержен SQL-инъекции. Конечно, переработайте процесс, чтобы избежать этого, однако он соответствует вашим потребностям.
/*
Summary: Lists file directory contents.
Remarks: - stackoverflow.com/q/10298910
- This assumes that the tree is put in order where
subfolders are listed right under their parent
folders. If this changes in the future, a
different logic will need to be implemented.
Example: exec directory 'c:\somepath', 3, '##results';
select * from ##results;
*/
create procedure directory
@root nvarchar(255),
@depth int,
@outputTable sysname
as
-- initializations
if @outputTable is null or not (left(@outputTable,2) = '##') or charindex(' ', @outputTable) > 0
throw 50000, '@outputTable must be a global temp table with no spaces in the name.', 1;
if exists (select 0 from tempdb.information_schema.tables where table_name = @outputTable)
begin
declare @msg nvarchar(255) = '''tempdb.dbo.' + @outputTable + ''' already exists.';
throw 50000, @msg, 1;
end
-- fetch the tree (it doesn't have full path names)
drop table if exists #dir;
create table #dir (
id int identity(1,1),
parentId int null,
path nvarchar(4000),
depth int,
isFile bit,
isLeader int default(0),
groupId int
)
insert #dir (path, depth, isFile)
exec xp_dirtree @root, @depth, 1;
-- identify the group leaders (based on a change in depth)
update d
set isLeader = _isLeader
from (
select id,
isLeader,
_isLeader = iif(depth - lag(depth) over(order by id) = 0, 0, 1)
from #dir
) d;
-- find the parents for each leader (subsetting just for leaders improves efficiency)
update #dir
set parentId = (
select max(sub.id)
from #dir sub
where sub.depth = d.depth - 1
and sub.id < d.id
and d.isLeader = 1
)
from #dir d
where d.isLeader = 1;
-- assign an identifier to each group (groups being objects that are 'siblings' of the leader)
update d
set groupId = _groupId
from (
select *, _groupId = sum(isLeader) over(order by id)
from #dir
) d;
-- set the parent id for each item based on the leader's parent id
update d
set d.parentId = leads.parentId
from #dir d
join #dir leads
on d.groupId = leads.groupId
and leads.parentId is not null;
-- convert the path names to full path names and calculate path parts
drop table if exists #pathBuilderResults;
with pathBuilder as (
select id, parentId, origId = id, path, pseudoDepth = depth
from #dir
union all
select par.id,
par.parentId,
pb.origId,
path = par.path + '\' + pb.path,
pseudoDepth = pb.pseudoDepth - 1
from pathBuilder pb
join #dir par on pb.parentId = par.id
where pb.pseudoDepth >= 2
)
select path = @root + '\' + pb.path,
name = d.path,
nameNoExt = iif(ext.value is null, d.path, left(d.path, len(d.path) - len(ext.value) - 1)),
extension = ext.value,
d.isFile,
runtimeId = pb.origId,
parentId = d.parentId
into #pathBuilderResults
from pathBuilder pb
join #dir d on pb.origId = d.id
cross apply (select value = charindex('.', d.path)) dotPos
cross apply (select value = right(d.path, len(d.path) - dotPos.value)) pseudoExt
cross apply (select value = iif(d.isFile = 1 and dotPos.value > 0, pseudoExt.value, null)) ext
where pb.pseudoDepth = 1
order by pb.origId;
-- terminate
declare @sql nvarchar(max) = 'select * into ' + @outputTable + ' from #pathBuilderResults';
exec (@sql);
person
pwilcox
schedule
04.02.2021
