Post Views 10,580 views
Hey Guys!
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:
- Operações com arquivos utilizando OLE Automation no SQL Server
- SQL Server – Como listar, ler, escrever, copiar, excluir e mover arquivos com o CLR (C#)
Para ativar o xp_cmdshell, basta executar o comando abaixo:
|
1 2 3 4 5 |
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.
stpArquivo_ListarCódigo-fonte
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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
Stored Procedure que retorna a listagem de arquivos e subdiretórios de um determinado diretório.
stpArquivo_LerCódigo-fonte
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
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 |
Result:
Stored Procedure que lê um arquivo de texto e retornar o seu conteúdo em forma de tabela.
stpEscreve_ArquivoCódigo-fonte
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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 |
Result:
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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 |
Result:
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).
stpApaga_ArquivoCódigo-fonte
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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 |
Result:
Stored Procedure que exclui um arquivo físicamente no disco ou na rede.
stpCopia_ArquivoCódigo-fonte
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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:
Stored Procedure que copia um arquivo para um outro diretório, podendo ser local ou na rede.
stpMove_ArquivoCódigo-fonte
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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 |
Result:
Stored Procedure que move um arquivo para um outro diretório, podendo ser local ou na rede.
stpCria_DiretorioCódigo-fonte
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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 |
Result:
Stored Procedure que cria um diretório em um local especificado.
stpApaga_DiretorioCódigo-fonte
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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 |
Result:
Stored Procedure que apaga um diretório, com possibilidade de apagar também arquivos e sub-pastas.
Código-fonte
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 |
Result:
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.
And that's it, folks!
Espero que tenham gostado desse post e até a próxima.
Portuguese 
English
Tentei mover arquivo, mas deu “Access denied”. Que perfil falta?
Obrigado me ajudou bastante