22 сентября 2021

MS SQL Server. Получение из файловой системы списка папок и файлов

    Иногда работа с базами данных подкидывает не стандартные задачи. Например, недавно мне в скрипте MS SQL Server понадобилось получить из файловой системы список папок и файлов. Мои попытки сделать это через объект Scripting.FileSystemObject потерпели неудачу. Его метод GetFolder по имени папки возвращает объект Folder, у которого есть свойства SubFolders/Files содержащее списки вложенных папок и файлов. Но проблема в том, что эти списки – это коллекции, элементы которых в VBA можно перебрать в цикле "For Each File in Folder.Files", а из скрипта T-SQL к их элементам можно обратиться только по имени папки/файла (exec sp_OAMethod @objItems, N'Item("FileName.txt")', @objItem out). То есть для получения списка папок и файлов объект Scripting.FileSystemObject не подходит. Поиск в интернете позволил мне сформулировать 4 различные способа решения этой задачи.

    Для первого способа решения задачи все же предлагаю использовать механизм работы с объектами OLE из скрипта MS SQL Server. Только вместо Scripting.FileSystemObject воспользуемся объектом Shell.Application. Этот объект является интерфейсом к Windows Shell и позволит нам получить доступ к файловой системе. Для использования OLE-объектов в скриптах T-SQL серверу MS SQL Server необходимо дать разрешение (по умолчанию – отключено):

exec sp_configure 'Show advanced options', 1
go
reconfigure
go
exec sp_configure 'Ole Automation Procedures', 0
go
reconfigure
go
Теперь пишем сам скрипт:
declare @Path nvarchar(1024) = N'd:\RootFolder'

declare @Command nvarchar(1050),
        @Result int, 
        @objShellApplication int,
        @objFolder int,
        @ItemsCount int,
        @objItems int,
        @ItemIndex int = 0,
        @objItem int,

        @ItemPath nvarchar(1024),
        @ItemName nvarchar(260),
        @IsFolder bit

exec @Result = sp_OACreate 'Shell.Application', @objShellApplication out

set @Command = N'NameSpace("' + @Path + '")'
exec @Result = sp_OAMethod @objShellApplication, @Command, @objFolder out

exec @Result = sp_OAGetProperty @objFolder, N'Items.Count', @ItemsCount out

if @Result = 0 and @ItemsCount > 0
  begin
    exec @Result = sp_OAGetProperty @objFolder, N'Items', @objItems out
    while @Result = 0 and @ItemIndex < @ItemsCount
      begin
        set @Command = N'Item(' + cast(@ItemIndex as nvarchar(5)) + ')'
        exec @Result = sp_OAGetProperty @objItems, @Command, @objItem out

        if @Result = 0
          begin
            exec @Result = sp_OAGetProperty @objItem, N'Path',  @ItemPath out
            exec @Result = sp_OAGetProperty @objItem, N'Name',  @ItemName out
            exec @Result = sp_OAGetProperty @objItem, N'IsFolder',  @IsFolder out
        
            exec sp_OADestroy @objItem

            print '"' + @ItemPath + '" | "' + @ItemName + 
                  '" | IsFolder=' + cast(@IsFolder as char(1))
          end

        set @ItemIndex = @ItemIndex + 1 
      end
    exec sp_OADestroy @objFolder
  end
exec sp_OADestroy @objFolder
exec sp_OADestroy @objShellApplication
Что бы сократить скрипт и повысить его читаемость я убрал из него проверку результата выполнения команд ("if @Result = 0") и вызов sp_OAGetErrorInfo в случае ошибки.
Результат работы:
MS SQL Server - Shell.Application
Используя этот пример можно написать рекурсивную процедуру, которая составил список папок и файлов.

    Второй способ – это использование системной процедуры sys.xp_cmdshell. Она создает командную оболочку Windows и передает ей строку для выполнения. При выполнении процедура xp_cmdshell имеет те же права безопасности, что и учетная запись, под которой запущена служба MS SQL Server. По умолчанию ее использование отключено, поэтому сначала надо ее включить.

exec sp_configure 'Show advanced options', 1
go
reconfigure 
go
exec sp_configure 'xp_cmdshell', 1
go
reconfigure
go
Результаты работы процедуры xp_cmdshell возвращаются в виде текстовых строк, поэтому для дальнейшей обработки их можно поместить в временную таблицу.
declare @Path nvarchar(1024) = N'd:\RootFolder'

declare @Command nvarchar(1050)

create table #Dir (isFile bit not null constraint df_isFile default 0, 
                   ItemName nvarchar(260))

set @Command = N'dir "' + @Path + '" /B /A:D'
insert #Dir(ItemName)
exec sys.xp_cmdshell @Command

alter table #Dir drop constraint df_isFile
alter table #Dir add constraint df_isFile default 1 for isFile

set @Command = N'dir "' + @Path + '" /B /A:-D'
insert #Dir(ItemName)
exec sys.xp_cmdshell @Command

delete from #Dir where ItemName is null

select isFile, ItemName
  from #Dir 
order by isFile, ItemName

drop table #Dir
Результат работы:
MS SQL Server - sys.xp_cmdshell
Для получения списка с вложенными папками и файлами опять необходимо писать рекурсивную процедуру.

    Третий способ – использование недокументированной хранимой процедуры sys.xp_dirtree. Она возвращает список папок и файлов по указанному пути. Для ее работы необходимо включить на сервере расширенные хранимые процедуры SQL Server Management Objects (SMO) и Distributed Management Objects (DMO):

exec sp_configure 'Show advanced options', 1
go
reconfigure
go
exec sp_configure 'SMO and DMO XPs', 1
go
reconfigure
go
Интересно, что начиная с SQL Server 2012 (11.x), DMO были удалены из SQL Server, но параметр по-прежнему называется "SMO and DMO XP".
    У процедуры xp_dirtree три параметра:
  • directory – путь для получения списка папок и файлов;
  • depth – сколько уровней вложенных папок следует сканировать (по умолчанию 0 – все подпапки);
  • file – кроме списка папок возвращать список файлов (по умолчанию 0 – отображать только папки).
Примеры использования:
  • Список всех подпапок
    exec sys.xp_dirtree N'd:\RootFolder', 0, 0
    MS SQL Server - sys.xp_dirtree (Список всех подпапок )
  • Список всех подпапок двух уровней
    exec sys.xp_dirtree N'd:\RootFolder', 2, 0
    MS SQL Server - sys.xp_dirtree (Список всех подпапок двух уровней)
  • Список папок и файлов в указанной папке
    exec sys.xp_dirtree N'd:\RootFolder', 1, 1
    MS SQL Server - sys.xp_dirtree (Список папок и файлов в указанной папке)
Как и с предыдущей процедурой нам понадобится временная таблица, а для получения информации по всем вложенным папкам необходимо написать рекурсивную процедуру.
declare @Path nvarchar(1024) = N'd:\RootFolder'

create table #DirTree
  (SubDirectory nvarchar(260),
   Depth  int,
   isFile int)
      
insert #DirTree(SubDirectory, Depth, isFile)
exec sys.xp_dirtree @Path, 1, 1
      
declare @Name nvarchar(260), @isFile int
declare DirTree cursor local read_only forward_only for
  select SubDirectory, isFile 
    from #DirTree 
  order by isFile, SubDirectory
      
open DirTree
fetch next from DirTree into @Name, @isFile
while @@fetch_status = 0
  begin
    -- обработка папок и файлов
    if @isFile = 0
      print 'Folder: ' + @Name
    else 
      print 'File  : ' + @Name
    fetch next from DirTree into @Name, @isFile
  end
close DirTree
deallocate DirTree

drop table #DirTree
Результат работы:
MS SQL Server - sys.xp_dirtree

    Пользователям MS SQL Server 2017 и более новых версий СУБД повезло больше. Им для работы с файловой системой доступен еще один способ – динамические административные представления (Dynamic Management Views – DMV) и динамические административные функции (Dynamic Management Functions – DMF). Для использования DMV и DMF так же необходимо включить "SMO и DMO XPs".
    Четвертый способ – использование функции sys.dm_os_enumerate_filesystem. Она возвращает список всех папок и файлов по указанному пути. Функция имеет два параметра:

  • initial_directory – путь для получения списка папок и файлов;
  • search_pattern – маска для фильтрации файлов.
Примеры использования:
  • Все папки и файлы
    select *
      from sys.dm_os_enumerate_filesystem(N'd:\RootFolder', N'*.*')
    MS SQL Server - sys.dm_os_enumerate_filesystem (Все папки и файлы)
  • Список файлов с расширением TXT
    select *
      from sys.dm_os_enumerate_filesystem(N'd:\RootFolder', N'*.txt')
    MS SQL Server - sys.dm_os_enumerate_filesystem (Список файлов с расширением TXT)
  • Список файлов только в указанной папке
    select *
      from sys.dm_os_enumerate_filesystem(N'd:\RootFolder', N'*.txt')
     where level = 0
    MS SQL Server - sys.dm_os_enumerate_filesystem (Список файлов только в указанной папке)
Как видите, функция dm_os_enumerate_filesystem – самый информативный и простой в использовании вариант.


P.S. Бонус на тему DMV&DMF:

  • вьюшка sys.dm_os_enumerate_fixed_drives – список смонтированных дисков
    select *
      from sys.dm_os_enumerate_fixed_drives
    MS SQL Server - sys.dm_os_enumerate_fixed_drives
  • функция sys.dm_os_file_exists – проверяет существование на диске файла или папки
    select *
      from sys.dm_os_file_exists(N'd:\RootFolder\File-level 1-2.jpg')
    MS SQL Server - sys.dm_os_file_exists

Комментариев нет:

Отправить комментарий