Как просмотреть файлы внутри папки с помощью SQL Server

Ответ 1

Вы можете использовать xp_dirtree

Требуется три параметра:

Путь корневого каталога, Глубина, до которой вы хотите получить файлы и папки и последний из них предназначен для отображения только папок или обеих папок и файлов.

ПРИМЕР: EXEC xp_dirtree 'C:\', 10, 1

Ответ 2

Может быть выполнено с помощью xp_DirTree, затем прокручивается, чтобы создать полный путь к файлу, если это необходимо.

Вот выдержка из script, которую я использую для автоматического восстановления баз данных на тестовом сервере. Он сканирует папку и все подпапки для любых файлов резервных копий, а затем возвращает полный путь.

  DECLARE @BackupDirectory SYSNAME = @BackupFolder

  IF OBJECT_ID('tempdb..#DirTree') IS NOT NULL
    DROP TABLE #DirTree

  CREATE TABLE #DirTree (
    Id int identity(1,1),
    SubDirectory nvarchar(255),
    Depth smallint,
    FileFlag bit,
    ParentDirectoryID int
   )

   INSERT INTO #DirTree (SubDirectory, Depth, FileFlag)
   EXEC master..xp_dirtree @BackupDirectory, 10, 1

   UPDATE #DirTree
   SET ParentDirectoryID = (
    SELECT MAX(Id) FROM #DirTree d2
    WHERE Depth = d.Depth - 1 AND d2.Id < d.Id
   )
   FROM #DirTree d

  DECLARE 
    @ID INT,
    @BackupFile VARCHAR(MAX),
    @Depth TINYINT,
    @FileFlag BIT,
    @ParentDirectoryID INT,
    @wkSubParentDirectoryID INT,
    @wkSubDirectory VARCHAR(MAX)

  DECLARE @BackupFiles TABLE
  (
    FileNamePath VARCHAR(MAX),
    TransLogFlag BIT,
    BackupFile VARCHAR(MAX),    
    DatabaseName VARCHAR(MAX)
  )

  DECLARE FileCursor CURSOR LOCAL FORWARD_ONLY FOR
  SELECT * FROM #DirTree WHERE FileFlag = 1

  OPEN FileCursor
  FETCH NEXT FROM FileCursor INTO 
    @ID,
    @BackupFile,
    @Depth,
    @FileFlag,
    @ParentDirectoryID  

  SET @wkSubParentDirectoryID = @ParentDirectoryID

  WHILE @@FETCH_STATUS = 0
  BEGIN
    --loop to generate path in reverse, starting with backup file then prefixing subfolders in a loop
    WHILE @wkSubParentDirectoryID IS NOT NULL
    BEGIN
      SELECT @wkSubDirectory = SubDirectory, @wkSubParentDirectoryID = ParentDirectoryID 
      FROM #DirTree 
      WHERE ID = @wkSubParentDirectoryID

      SELECT @BackupFile = @wkSubDirectory + '\' + @BackupFile
    END

    --no more subfolders in loop so now prefix the root backup folder
    SELECT @BackupFile = @BackupDirectory + @BackupFile

    --put backupfile into a table and then later work out which ones are log and full backups  
    INSERT INTO @BackupFiles (FileNamePath) VALUES(@BackupFile)

    FETCH NEXT FROM FileCursor INTO 
      @ID,
      @BackupFile,
      @Depth,
      @FileFlag,
      @ParentDirectoryID 

    SET @wkSubParentDirectoryID = @ParentDirectoryID      
  END

  CLOSE FileCursor
  DEALLOCATE FileCursor

Ответ 3

Создайте сборку SQLCLR с правами на доступ к внешнему виду, которая возвращает список файлов в виде набора результатов. Есть много примеров, как это сделать, например. Еще один TVF: возврат файлов из каталога или Торговля xp_cmdshell для SQLCLR (часть 1) - Список содержимого каталога.

Ответ 4

Если вы хотите, вы можете достичь этого, используя функцию/сборку CLR.

• Создайте проект сборки CLR для SQL Server.
• Перейдите к свойствам и убедитесь, что уровень разрешений на подключении настроен на внешний
• Добавить функцию Sql в сборку.

Вот пример, который позволит вам выбрать форму вашего результирующего набора, подобную таблице.

public partial class UserDefinedFunctions
{
    [SqlFunction(DataAccess = DataAccessKind.Read,
        FillRowMethodName = "GetFiles_FillRow", TableDefinition = "FilePath nvarchar(4000)")]
    public static IEnumerable GetFiles(SqlString path)
    {
        return System.IO.Directory.GetFiles(path.ToString()).Select(s => new SqlString(s));
    }

    public static void GetFiles_FillRow(object obj,out SqlString filePath)
    {
        filePath = (SqlString)obj;
    }
};

И ваш SQL-запрос.

use MyDb

select * From GetFiles('C:\Temp\');

Помните, что в вашей базе данных должна быть включена функциональность CLR Assembly с помощью следующей команды SQL.

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Сборка CLR (например, XP_CMDShell) отключена по умолчанию, поэтому, если причина, по которой не используется XP Cmd Shell, состоит в том, что у вас нет разрешения, вы можете застрять в этой опции как ну... просто FYI.

Ответ 5

Я долго искал, чтобы найти достойное решение для этого, и в конце концов нашел несколько смехотворно сложных решений CLR, поэтому решил написать мой собственный простой VB. Просто создайте новый проект VB CLR на вкладке "База данных" в разделе "Установленные шаблоны", а затем добавьте новую пользовательскую функцию SQL CLR VB. Я переименовал его в CLRGetFilesInDir.vb. Вот код внутри него...

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.IO
-----------------------------------------------------------------------------
Public Class CLRFilesInDir
-----------------------------------------------------------------------------
<SqlFunction(FillRowMethodName:="FillRowFiles", IsDeterministic:=True, IsPrecise:=True, TableDefinition:="FilePath nvarchar(4000)")> _
Public Shared Function GetFiles(PathName As SqlString, Pattern As SqlString) As IEnumerable
    Dim FileNames As String()

    Try
    FileNames = Directory.GetFiles(PathName, Pattern, SearchOption.TopDirectoryOnly)
    Catch
        FileNames = Nothing
    End Try

    Return FileNames

End Function
-----------------------------------------------------------------------------
Public Shared Sub FillRowFiles(ByVal obj As Object, ByRef Val As SqlString)
    Val = CType(obj, String).ToString
End Sub

End Class

Я также изменил имя сборки в окне "Свойства проекта" на CLRExcelFiles, а пространство имен по умолчанию - CLRGetExcelFiles.

ПРИМЕЧАНИЕ. Установите целевую структуру в 3.5, если вы используете что-то меньшее, чем SQL Server 2012.

Скомпилируйте проект, а затем скопируйте файл CLRExcelFiles.dll из \bin\release куда-то вроде C:\temp на машине SQL Server, а не в свой собственный.

В SSMS: -

CREATE ASSEMBLY <your assembly name in here - anything you like>
FROM 'C:\temp\CLRExcelFiles.dll';

CREATE FUNCTION dbo.fnGetFiles
(
@PathName NVARCHAR(MAX),
@Pattern NVARCHAR(MAX)
)
RETURNS TABLE (Val NVARCHAR(100))
AS
EXTERNAL NAME <your assembly name>."CLRGetExcelFiles.CLRFilesInDir".GetFiles;
GO

затем назовите его

SELECT * FROM dbo.fnGetFiles('\\<SERVERNAME>\<$SHARE>\<folder>\' , '*.xls')

ПРИМЕЧАНИЕ. Несмотря на то, что я изменил уровень разрешений на EXTERNAL_ACCESS на вкладке SQLCLR в разделе "Свойства проекта", мне все равно нужно было запускать его каждый раз, когда я (re) создал его.

ALTER ASSEMBLY [CLRFilesInDirAssembly] 
WITH PERMISSION_SET = EXTERNAL_ACCESS 
GO

и wullah! которые должны работать.

Ответ 6

Очень просто, просто используйте синтаксис SQLCMD.

Не забудьте включить SQLCMD-режим в SSMS, посмотрите в разделе Query → Режим SQLCMD

Попробуйте выполнить:

!! DIR
!!: GO

или, может быть:
  !! DIR "c:/temp"
  !!: GO