Olá pessoal!

Neste post, vou demonstrar como realizar operações com arquivos utilizando a procedure xp_cmdshell, como copiar arquivos, excluir, renomear, mover, criar diretórios, etc.

Esse post é um complemento de outros de operações com arquivos, utilizando outras soluções:

Para ativar o xp_cmdshell, basta executar o comando abaixo:

sp_configure 'advanced options', 1
RECONFIGURE

sp_configure 'xp_cmdshell', 1
RECONFIGURE

stpArquivo_Existe
Stored Procedure que retorna uma variável booleana (BIT) informando se um determinado arquivo ou diretório existe.

Código-fonte

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

Exemplos de utilização

Verificando se um diretório existe

Verificando se um arquivo existe

stpArquivo_Listar
Stored Procedure que retorna a listagem de arquivos e subdiretórios de um determinado diretório.

Código-fonte

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:

stpArquivo_Ler
Stored Procedure que lê um arquivo de texto e retornar o seu conteúdo em forma de tabela.

Código-fonte

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 vocês devem ter reparado, a importação de arquivos com acentuação não funciona muito bem, pois o comando type do Prompt do DOS, mesmo no Windows em português, não trabalha muito bem com isso. Como alternativa, podemos utilizar o velho conhecido Power Shell.

Código-fonte em 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:

stpEscreve_Arquivo
Stored Procedure que grava um texto em um arquivo. Devido à uma limitação do Prompt do DOS, não é possível gravar textos com quebras de linhas (para isso, utilize a solução com OLE Automation ou CLR).

Código-fonte

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:

stpApaga_Arquivo
Stored Procedure que exclui um arquivo físicamente no disco ou na rede.

Código-fonte

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 apagando um arquivo:

Resultado utilizando caractere curinga:

stpCopia_Arquivo
Stored Procedure que copia um arquivo para um outro diretório, podendo ser local ou na rede.

Código-fonte

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_Arquivo
Stored Procedure que move um arquivo para um outro diretório, podendo ser local ou na rede.

Código-fonte

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:

stpCria_Diretorio
Stored Procedure que cria um diretório em um local especificado.

Código-fonte

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:

stpApaga_Diretorio
Stored Procedure que apaga um diretório, com possibilidade de apagar também arquivos e sub-pastas.

Código-fonte

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 já comentei em alguns posts aqui no blog, não sou nada fã de habilitar e utilizar o xp_cmdshell, uma vez que ele dá “poderes” para qualquer usuário sysadmin da instância executar qualquer comando que o usuário que inicia o serviço do SQL Server possui privilégio, desde um comando dir, até executar delete, format, etc..

Por este motivo, prefiro adotar a utilização do CLR, onde você programa as rotinas utilizando a linguagem de programação C# e o .NET Framework e publica as rotinas na instância. Tanto os usuários sysadmin quanto os usuários normais só conseguem utilizar os métodos implementados na biblioteca CLR, não permitindo a execução de códigos maliciosos que não estejam programados, mas entendo que nem todo DBA quer ter o trabalho de criar bibliotecas em C# e acaba optando por utilizar uma abordagem mais simples, utilizando xp_cmdshell e comandos do DOS e/ou PowerShell.

É isso aí, pessoal!
Espero que tenham gostado desse post e até a próxima.