¡Hola, chicos!

En esta publicación, demostraré cómo realizar operaciones con archivos usando el procedimiento xp_cmdshell, como copiar archivos, eliminarlos, renombrarlos, moverlos, crear directorios, etc.

Esta publicación es un complemento a otras sobre operaciones de archivos, utilizando otras soluciones:

Para activar xp_cmdshell, simplemente ejecute el siguiente comando:

sp_configure 'advanced options', 1
RECONFIGURE

sp_configure 'xp_cmdshell', 1
RECONFIGURE

stpFile_Exists
Procedimiento almacenado que devuelve una variable booleana (BIT) que informa si existe un archivo o directorio determinado.

código fuente

CREATE PROCEDURE dbo.stpArquivo_Existe (
    @Ds_Arquivo VARCHAR(255),
    @Saida BIT OUTPUT
)
AS BEGIN

    DECLARE @Query VARCHAR(8000) = 'IF EXIST "' + @Ds_Arquivo + '" ( echo 1 ) ELSE ( echo 0 )'

    DECLARE @Retorno TABLE (
        Linha INT IDENTITY(1, 1),
        Resultado VARCHAR(MAX)
    )

    INSERT INTO @Retorno
    EXEC master.dbo.xp_cmdshell 
        @command_string = @Query

    SELECT @Saida = Resultado
    FROM @Retorno
    WHERE Linha = 1

END
GO

Ejemplos de uso

Comprobando si existe un directorio

Comprobando si existe un archivo

stpFile_List
Procedimiento almacenado que devuelve una lista de archivos y subdirectorios en un directorio determinado.

código fuente

CREATE PROCEDURE dbo.stpArquivo_Listar (
    @Ds_Diretorio VARCHAR(255)
)
AS BEGIN
    
    DECLARE @Query VARCHAR(8000) = 'dir/ -C /4 /N "' + @Ds_Diretorio + '"'

    DECLARE @Retorno TABLE (
        Linha INT IDENTITY(1, 1),
        Resultado VARCHAR(MAX)
    )

    DECLARE @Tabela_Final TABLE (
        Linha INT IDENTITY(1, 1),
        Dt_Criacao DATETIME,
        Fl_Tipo BIT,
        Qt_Tamanho INT,
        Ds_Arquivo VARCHAR(255)
    )

    INSERT INTO @Retorno
    EXEC master.dbo.xp_cmdshell 
        @command_string = @Query


    INSERT INTO @Tabela_Final(Dt_Criacao, Fl_Tipo, Qt_Tamanho, Ds_Arquivo)
    SELECT 
        CONVERT(DATETIME, LEFT(Resultado, 17), 103) AS Dt_Criacao,
        0 AS Fl_Tipo, 
        0 AS Qt_Tamanho,
        SUBSTRING(Resultado, 37, LEN(Resultado)) AS Ds_Arquivo
    FROM 
        @Retorno
    WHERE 
        Resultado IS NOT NULL
        AND Linha >= 6
        AND Linha < (SELECT MAX(Linha) FROM @Retorno) - 2
        AND Resultado LIKE '%<DIR>%'
        AND SUBSTRING(Resultado, 37, LEN(Resultado)) NOT IN ('.', '..')
    ORDER BY
        Ds_Arquivo
        

    INSERT INTO @Tabela_Final(Dt_Criacao, Fl_Tipo, Qt_Tamanho, Ds_Arquivo)
    SELECT 
        CONVERT(DATETIME, LEFT(Resultado, 17), 103) AS Dt_Criacao,
        1 AS Fl_Tipo, 
        LTRIM(SUBSTRING(LTRIM(Resultado), 18, 19)) AS Qt_Tamanho,
        SUBSTRING(Resultado, CHARINDEX(LTRIM(SUBSTRING(LTRIM(Resultado), 18, 19)), Resultado, 18) + LEN(LTRIM(SUBSTRING(LTRIM(Resultado), 18, 19))) + 1, LEN(Resultado)) AS Ds_Arquivo
    FROM 
        @Retorno
    WHERE 
        Resultado IS NOT NULL
        AND Linha >= 6
        AND Linha < (SELECT MAX(Linha) FROM @Retorno) - 2
        AND Resultado NOT LIKE '%<DIR>%'
    ORDER BY
        Ds_Arquivo

        
    --SELECT * FROM @Retorno
    SELECT * FROM @Tabela_Final


END
GO

Resultado:

stpFile_Read
Procedimiento almacenado que lee un archivo de texto y devuelve su contenido en forma de tabla.

código fuente

CREATE PROCEDURE dbo.stpArquivo_Ler (
    @Ds_Arquivo VARCHAR(255)
)
AS BEGIN
    
    DECLARE @Query VARCHAR(8000) = 'type "' + @Ds_Arquivo + '"'

    DECLARE @Retorno TABLE (
        Linha INT IDENTITY(1, 1),
        Resultado VARCHAR(MAX)
    )

    INSERT INTO @Retorno
    EXEC master.dbo.xp_cmdshell 
        @command_string = @Query
    
    SELECT 
        Linha,
        Resultado
    FROM @Retorno
    
END
GO

Resultado:

Como habrás notado, importar archivos con acentos no funciona muy bien, porque el comando de tipo en el indicador de DOS, incluso en Windows en portugués, no funciona muy bien con esto. Como alternativa podemos utilizar el conocido Power Shell.

Código fuente en Powershell

CREATE PROCEDURE dbo.stpArquivo_Ler (
    @Ds_Arquivo VARCHAR(255)
)
AS BEGIN
    
    DECLARE @Query VARCHAR(8000) = 'powershell.exe -ExecutionPolicy Bypass -Command "Get-Content ""' + @Ds_Arquivo + '"""'

    DECLARE @Retorno TABLE (
        Linha INT IDENTITY(1, 1),
        Resultado VARCHAR(MAX)
    )

    INSERT INTO @Retorno
    EXEC master.dbo.xp_cmdshell 
        @command_string = @Query
    
    SELECT * FROM @Retorno
    
END

Resultado:

stpWrite_File
Procedimiento almacenado que escribe texto en un archivo. Debido a una limitación del Prompt de DOS, no es posible grabar textos con saltos de línea (para ello utilice la solución con OLE Automation o CLR).

código fuente

CREATE PROCEDURE dbo.stpEscreve_Arquivo (
    @Ds_Arquivo VARCHAR(255),
    @Ds_Texto VARCHAR(MAX),
    @Fl_Sobrescrever BIT = 0
)
AS BEGIN
    
    SET NOCOUNT ON

    DECLARE @Query VARCHAR(8000) = 'ECHO ' + @Ds_Texto + (CASE WHEN @Fl_Sobrescrever = 1 THEN ' > ' ELSE ' >> ' END) + ' ' + @Ds_Arquivo + '"'

    DECLARE @Retorno TABLE ( Resultado VARCHAR(MAX) )
    
    INSERT INTO @Retorno
    EXEC master.dbo.xp_cmdshell
        @command_string = @Query
    
END

Resultado:

stpDelete_File
Procedimiento almacenado que elimina un archivo físicamente en el disco o en la red.

código fuente

CREATE PROCEDURE dbo.stpApaga_Arquivo (
    @Ds_Arquivo VARCHAR(255)
)
AS BEGIN
    
    SET NOCOUNT ON

    DECLARE @Query VARCHAR(8000) = 'del /F /Q "' + @Ds_Arquivo + '"'

    DECLARE @Retorno TABLE ( Resultado VARCHAR(MAX) )

    INSERT INTO @Retorno
    EXEC master.dbo.xp_cmdshell 
        @command_string = @Query
    
END

Resultado al eliminar un archivo:

Resultado usando comodín:

stpCopy_File
Procedimiento almacenado que copia un archivo a otro directorio, que puede ser local o de red.

código fuente

CREATE PROCEDURE dbo.stpCopia_Arquivo (
    @Ds_Arquivo VARCHAR(255),
    @Ds_Diretorio VARCHAR(255),
    @Fl_Sobrescrever BIT = 0
)
AS BEGIN
    

    SET NOCOUNT ON


    DECLARE 
        @Query VARCHAR(8000),
        @Nm_Arquivo_Destino VARCHAR(500) = @Ds_Diretorio + REVERSE(LEFT(REVERSE(@Ds_Arquivo),CHARINDEX('\', REVERSE(@Ds_Arquivo), 1) - 1)),
        @Resultado VARCHAR(MAX)

    
    IF (@Fl_Sobrescrever = 0)
        SET @Query = 'IF EXIST "' + @Nm_Arquivo_Destino + '" ( ECHO Arquivo já existe ) ELSE ( copy "' + @Ds_Arquivo + '" "' + @Ds_Diretorio + '")'
    ELSE
        SET @Query = 'copy ' + (CASE WHEN @Fl_Sobrescrever = 1 THEN '/Y' ELSE '' END) + ' "' + @Ds_Arquivo + '" "' + @Ds_Diretorio + '"'


    DECLARE @Retorno TABLE (
        Linha INT IDENTITY(1, 1),
        Resultado VARCHAR(MAX)
    )

    INSERT INTO @Retorno
    EXEC master.dbo.xp_cmdshell 
        @command_string = @Query
    

    SELECT @Resultado = LTRIM(RTRIM(Resultado))
    FROM @Retorno
    WHERE Linha = 1
    
    PRINT @Resultado

END

Resultado:

stpMove_File
Procedimiento almacenado que mueve un archivo a otro directorio, que puede ser local o de red.

código fuente

CREATE PROCEDURE dbo.stpMove_Arquivo (
    @Ds_Arquivo VARCHAR(255),
    @Ds_Diretorio VARCHAR(255),
    @Fl_Sobrescrever BIT = 0
)
AS BEGIN
    

    SET NOCOUNT ON


    DECLARE 
        @Query VARCHAR(8000),
        @Nm_Arquivo_Destino VARCHAR(500) = @Ds_Diretorio + REVERSE(LEFT(REVERSE(@Ds_Arquivo),CHARINDEX('\', REVERSE(@Ds_Arquivo), 1) - 1)),
        @Resultado VARCHAR(MAX)

    
    IF (@Fl_Sobrescrever = 0)
        SET @Query = 'IF EXIST "' + @Nm_Arquivo_Destino + '" ( ECHO Arquivo já existe ) ELSE ( move "' + @Ds_Arquivo + '" "' + @Ds_Diretorio + '")'
    ELSE
        SET @Query = 'move ' + (CASE WHEN @Fl_Sobrescrever = 1 THEN '/Y' ELSE '' END) + ' "' + @Ds_Arquivo + '" "' + @Ds_Diretorio + '"'


    DECLARE @Retorno TABLE (
        Linha INT IDENTITY(1, 1),
        Resultado VARCHAR(MAX)
    )

    INSERT INTO @Retorno
    EXEC master.dbo.xp_cmdshell 
        @command_string = @Query
    

    SELECT @Resultado = LTRIM(RTRIM(Resultado))
    FROM @Retorno
    WHERE Linha = 1
    
    PRINT @Resultado

END

Resultado:

stpCreate_Directorio
Procedimiento almacenado que crea un directorio en una ubicación específica.

código fuente

CREATE PROCEDURE dbo.stpCria_Diretorio (
    @Ds_Diretorio VARCHAR(255)
)
AS BEGIN
    
    SET NOCOUNT ON

    DECLARE @Query VARCHAR(8000) = 'mkdir "' + @Ds_Diretorio + '"'

    DECLARE @Retorno TABLE ( Resultado VARCHAR(MAX) )

    INSERT INTO @Retorno
    EXEC master.dbo.xp_cmdshell 
        @command_string = @Query
    
END

Resultado:

stpDelete_Directorio
Procedimiento almacenado que elimina un directorio, con posibilidad de eliminar también archivos y subcarpetas.

código fuente

CREATE PROCEDURE dbo.stpApaga_Diretorio (
    @Ds_Diretorio VARCHAR(255),
    @Fl_Recursivo BIT = 0
)
AS BEGIN
    
    SET NOCOUNT ON

    DECLARE @Query VARCHAR(8000) = 'rmdir' + (CASE WHEN @Fl_Recursivo = 1 THEN ' /S' ELSE '' END) + ' "' + @Ds_Diretorio + '"'

    DECLARE @Retorno TABLE ( Resultado VARCHAR(MAX) )

    INSERT INTO @Retorno
    EXEC master.dbo.xp_cmdshell 
        @command_string = @Query
    
END

Resultado:

Como ya lo he mencionado en algunas publicaciones aquí en el blog, no soy partidario de habilitar y usar xp_cmdshell, ya que le da “poderes” a cualquier usuario sysadmin de la instancia para ejecutar cualquier comando que el usuario que inicia el servicio SQL Server tenga privilegios, desde un comando dir, hasta ejecutar borrar, formatear, etc.

Por esta razón, prefiero adoptar el uso de CLR, donde programa las rutinas usando el lenguaje de programación C# y .NET Framework y publica las rutinas en la instancia. Tanto los usuarios sysadmin como los usuarios normales solo pueden usar los métodos implementados en la biblioteca CLR, no permitiendo la ejecución de código malicioso que no esté programado, pero entiendo que no todos los DBA quieren tomarse la molestia de crear bibliotecas en C# y terminan eligiendo usar un enfoque más simple, usando xp_cmdshell y comandos de DOS y/o PowerShell.

¡Eso es todo, amigos!
Espero que hayas disfrutado de esta publicación y nos vemos la próxima.