Для первого способа решения задачи все же предлагаю использовать механизм работы с объектами 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 в случае ошибки.Результат работы:
Второй способ – это использование системной процедуры 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
Результат работы:
Третий способ – использование недокументированной хранимой процедуры 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
- Список всех подпапок двух уровней
exec sys.xp_dirtree N'd:\RootFolder', 2, 0
- Список папок и файлов в указанной папке
exec sys.xp_dirtree N'd:\RootFolder', 1, 1
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 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'*.*')
- Список файлов с расширением TXT
select * from sys.dm_os_enumerate_filesystem(N'd:\RootFolder', N'*.txt')
- Список файлов только в указанной папке
select * from sys.dm_os_enumerate_filesystem(N'd:\RootFolder', N'*.txt') where level = 0
P.S. Бонус на тему DMV&DMF:
- вьюшка sys.dm_os_enumerate_fixed_drives – список смонтированных дисков
select * from 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')
Комментариев нет:
Отправить комментарий