Cómo listr files dentro de una carpeta con SQL Server

¿Cómo incluyo los files dentro de una carpeta en SQL Server sin usar el procedimiento almacenado xp_cmdshell ?

Puedes usar xp_dirtree

Toma tres parameters:

Ruta de un directory raíz , profundidad hasta la que desea get files y carpetas y la última es para mostrar solo las carpetas o ambas carpetas y files.

EJEMPLO: EXEC xp_dirtree 'C:\', 10, 1

Se puede hacer usando xp_DirTree, luego haciendo un bucle para generar una ruta de file completa si es necesario.

Aquí hay un extracto de un script que utilizo para restaurar bases de datos a un server de testing automáticamente. Escanea una carpeta y todas las subcarpetas para cualquier file de respaldo, luego regresa la ruta completa.


  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 

Cree un ensamblado SQLCLR con permiso de acceso externo que devuelva la list de files como un set de resultados. Hay muchos ejemplos de cómo hacer esto, ej. Sin embargo, otro TVF: devolver files desde un directory o Trading en xp_cmdshell para SQLCLR (Parte 1) – Lista de contenidos del directory .

Si lo desea, puede lograrlo utilizando una function / set CLR.

  1. Crear un proyecto de ensamblado CLR de SQL Server.
  2. Vaya a properties y asegúrese de que el nivel de permiso en la connection esté configurado como externo
  3. Agregue una function Sql al ensamblaje.

Aquí hay un ejemplo que le permitirá seleccionar su set de resultados como una tabla.

 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; } }; 

Y tu consulta SQL.

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

Sin embargo, tenga en count que su database debe tener habilitada la Asamblea CLR utilizando el siguiente Comando SQL.

 sp_configure 'clr enabled', 1 GO RECONFIGURE GO 

Los ensamblajes de CLR (como XP_CMDShell ) están deshabilitados de forma pnetworkingeterminada, por lo que si el motivo por el que no se usa XP Cmd Shell es porque no tiene permiso , entonces puede que también se quede con esta opción … solo FYI.

Busqué durante años para encontrar una solución fácil decente para esto y al final encontré algunas soluciones CLR ridículamente complicadas, así que decidí escribir mi propio VB simple. Simplemente cree un nuevo proyecto VB CLR desde la pestaña Base de datos en Plantillas instaladas , y luego agregue una nueva function SQL CLR VB definida por el usuario . Lo renombré a CLRGetFilesInDir.vb . Aquí está el código dentro de él …

 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 Shanetworking 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 Shanetworking Sub FillRowFiles(ByVal obj As Object, ByRef Val As SqlString) Val = CType(obj, String).ToString End Sub End Class 

También cambié el Nombre del ensamblado en la window Propiedades del proyecto a CLRExcelFiles y el Espacio de nombre pnetworkingeterminado a CLRGetExcelFiles .

NOTA: Establezca el marco de destino en 3.5 si está utilizando algo less que SQL Server 2012.

Compile el proyecto y luego copie CLRExcelFiles.dll de \ bin \ release en algún lugar como C: \ temp en el equipo de SQL Server , no el suyo.

En 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 

entonces llámalo

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

NOTA: Aunque cambié el Nivel de permiso a EXTERNAL_ACCESS en la pestaña SQLCLR en Propiedades del proyecto, igual necesitaba ejecutar esto cada vez que lo (re) creaba.

 ALTER ASSEMBLY [CLRFilesInDirAssembly] WITH PERMISSION_SET = EXTERNAL_ACCESS GO 

y wullah! eso debería funcionar.

Muy fácil, solo usa la syntax SQLCMD.

Recuerde habilitar el modo SQLCMD en el SSMS, busque en Consulta -> Modo SQLCMD

Intenta ejecutar:

!! DIR
!!:IR

o tal vez:
!! DIR "c: / temp"
!!:IR