O SQL Server possui uma das melhores ferramentas de gerenciamento denominadas SQL Server Management Studio (também conhecido como SSMS). Ele oferece muitos recursos que facilitam muito a vida de desenvolvedores e DBAs. Mas, às vezes, existem alguns problemas que não podem ser corrigidos usando o SQL Server Management Studio. Especialmente quando se trata de executar scripts SQL ad-hoc ou recuperar instâncias falhadas do SQL Server. Nesses casos de uso, você pode usar o SQLCMD.
Este é um artigo patrocinado da Devart. A Devart é atualmente um dos principais fornecedores de software de gerenciamento de banco de dados e soluções ALM para os servidores de banco de dados mais populares.
Introdução ao SQLCMD
Neste artigo, aprenderemos sobre o SQLCMD. É uma ferramenta de linha de comando que pode ser usada para as seguintes tarefas:
- Execute consultas SQL ad-hoc e procedimentos armazenados em servidores locais e remotos
- Exportar saída de consulta SQL para arquivos de texto ou CSV
- Gerenciar e administrar instâncias e bancos de dados do SQL Server no Windows e Linux
Para tornar a redação de consultas em SSMS mais rápido e fácil, bem como aprimorá -lo com recursos adicionais para gerenciamento e administração de banco de dados, aprimoramos -o com Ferramentas SQL DBFORGEum pacote de suplementos integrados perfeitamente ao SSMS.
Agora vamos começar com a instalação.
Para instalar o utilitário SQLCMD, você precisa selecionar as ferramentas do cliente SQL Server nativo durante a instalação do SQL Server. Você também pode instalá -lo separadamente usando o gerenciador de instalação do SQL Server.
O utilitário SQLCMD pode ser chamado apenas digitando SQLCMD no PowerShell ou no prompt de comando. Você pode ver a lista de opções que podem ser usadas com o SQLCMD executando o seguinte comando:
PS C:\Users\nisar> SQLCMD -?É assim que a saída da linha de comando é.
Conectando ao SQL Server usando SQLCMD
Agora, vamos entender como conectar a uma instância do SQL Server usando o SQLCMD.
Exemplo 1: conecte -se à instância padrão do SQL Server
Para conectar -se ao SQL Server em uma máquina local, use o seguinte comando SQLCMD:
C:\Users\nisar>sqlcmd -S Nisarg-PCComo você pode ver, a saída de comando é 1> que mostra que você está conectado ao SQL Server.
Observe que, se você estiver se conectando à instância padrão do SQL Server em uma máquina local, não precisará especificar explicitamente o nome do nome do host/servidor.
Exemplo 2: Conecte -se a uma instância nomeada do SQL Server
Agora, vamos verificar outro exemplo mostrando como conectar a uma instância nomeada do SQL Server.
Para se conectar a uma instância nomeada do SQL Server, você precisa especificar o parâmetro -s (nome do servidor), por exemplo, se o nome do seu servidor for MyServer e a instância nomeada é SQL2017o comando para se conectar a ele usando o SQLCMD seria:
C:\>sqlcmd -S Nisarg-PC\SQL2019Aqui está a saída.
Exemplo 3: Conecte -se ao SQL Server usando a autenticação do Windows e a autenticação do servidor SQL
Agora, vamos ver como conectar ao SQL Server usando a autenticação do Windows e SQL Server.
Para conectar -se ao SQL Server usando o SQLCMD, você pode usar a autenticação do Windows e a autenticação do SQL Server. Se você deseja usar a autenticação do SQL Server, precisa especificar as opções -u (nome de usuário) e -p (senha). Se você não especificar a senha, o utilitário SQLCMD solicitará que você insira a senha. A captura de tela a seguir ilustra isso.
Trabalhando com SQLCMD no modo interativo
Nesta seção, veremos como executar o SQLCMD no modo interativo, executar consultas SQL e visualizar a saída. O modo interativo permite gravar instruções e comandos SQL. Vamos começar aprendendo a conectar -se ao SQL Server, inserir o modo interativo e executar consultas no SQLCMD.
Exemplo 1: Preencha uma lista de bancos de dados com proprietários
Primeiro, conecte -se ao seu servidor de banco de dados usando o seguinte comando:
C:\>sqlcmd -S Nisarg-PC -U sa -pQuando a sessão interativa começar, execute a seguinte consulta SQL no utilitário SQLCMD:
use master;
select a.name,b.name from sys.databases a inner join sys.server_principals b
on a.owner_sid=b.sid where a.name not in ('ReportServer','ReportServerTempDB')
and a.database_id>5;Aqui está a saída da consulta.
Como você pode ver, a consulta acima povoou a lista de bancos de dados com proprietários de bancos de dados.
Exemplo 2: Verifique o banco de dados atual
Primeiro, conecte -se ao servidor de banco de dados e execute a seguinte consulta:
Select DB_NAME()
GoEsta é a saída de consulta.
A consulta retornou o banco de dados Master porque não configurei o banco de dados padrão para o login que estou usando para conectar ao SQL Server.
Exemplo 3: Executar consultas SQL
Você pode executar consultas SQL usando SQLCMD especificando o -Q parâmetro. Por exemplo, você deseja ver a lista de tabelas criadas no Gerenciamento de escola Banco de dados usando SQLCMD. O comando deve ser escrito da seguinte forma:
C:\>sqlcmd -S Nisarg-PC -d SchoolManagement -Q "select name from sys.tables"Dê uma olhada na saída da consulta.
Da mesma forma, você também pode executar outras consultas. Observe que o login que você está usando para conectar ao SQL Server deve ter a permissão necessária no banco de dados.
Trabalhando com SQLCMD no prompt de comando
É aqui que veremos como executar scripts SQL através do prompt de comando. Esse recurso é útil quando você deseja executar tarefas de automação, operações em massa e consultas de longa duração que não exigem nenhuma entrada do usuário.
Eu criei um script SQL que contém uma consulta SQL que é usada para preencher a lista de objetos criados no WideWorldImporters banco de dados. A consulta é a seguinte:
use (WideWorldImporters)
go
select name, type_desc, create_date from sys.objects where type_desc <>'SYSTEM_TABLE'Adicione a consulta acima a um script SQL nomeado sp_get_db_objects.sql. Agora vamos exportar a saída para um arquivo de texto nomeado Database_objects.txt.
Para isso, usaremos as seguintes opções:
- -O: especifique o arquivo de destino. Nesta demonstração, o arquivo de texto de destino é chamado Wideworldimportores_objects.txt.
- -i: especifique a localização do script SQL. Nesta demonstração, o script SQL é chamado DbObjects.sql.
Agora, vamos executar o seguinte comando:
sqlcmd -S Nisarg-PC -i D:\Scripts\DBObjects.sql -o D:\Scripts\WideWorldImportores_objects.txtDepois que o comando é concluído com sucesso, é hora de revisar o arquivo de texto.
Como você pode ver na captura de tela acima, a consulta foi executada com sucesso.
Agora, vamos dar outro exemplo. Aqui vamos aprender a gerar um backup de StackOverflow2010 usando um script SQL. A consulta para gerar um backup é a seguinte:
use master
go
backup database (Stackoverflow2010) to disk ='D:\SQLBackups\Stackoverflow2010.bak' with compression, stats=5Eu armazenei o comando de backup em um script SQL nomeado Stackoverflow2010_backup_script.sql. Para executar o script, o comando sqlcmd será o seguinte:
Captura de tela 1:
Como você pode ver na captura de tela acima, o backup foi gerado.
Captura de tela 2:
Usando o SQLCMD no SQL Server Management Studio
Para usar o SQLCMD no SSMS, primeiro, você deve ativar o modo SQLCMD. Para fazer isso, selecione Consulta No menu e selecione Modo sqlcmdcomo mostrado na imagem a seguir:
Se você deseja definir o modo SQLCMD por padrão, vá para Ferramentas → Opções. Em Opçõesselecione Execução de consulta → SQL Server → Em geral e selecione o Por padrão, abra novas consultas no modo SQLCMD Caixa de seleção.
Agora, vamos ver como usá -lo.
Por exemplo, eu quero obter os registros totais do Postagens Tabela do StackOverflow2010 banco de dados. Para fazer isso, a consulta deve ser escrita da seguinte forma:
:SETVAR TABLENAME "Posts"
:SETVAR DATABASENAME "Stackoverflow2010"
use $(DATABASENAME);
select count(1) from $(TABLENAME);
GOAgora, vamos executar a consulta. A captura de tela a seguir mostra a saída da consulta.
Agora, vamos ver como usar o SQLCMD no PowerShell.
Usando SQLCMD em PowerShell
Você pode invocar o SQLCMD usando o PowerShell. Para fazer isso, você deve instalar o PowerShell para o SQL Server. Você pode ler este artigo Para saber mais sobre o PowerShell para o SQL Server e como instalá -lo.
Vamos dar um exemplo simples. Suponha que eu queira obter a lista de procedimentos armazenados do WideWorldImporters banco de dados. O comando PowerShell é o seguinte:
PS C:\WINDOWS\system32> invoke-sqlcmd -database wideworldimporters -query "select name from sys.procedures"Aqui está a saída.
Outro exemplo mostra como exportar a saída de um script SQL para um arquivo de texto usando SQLPS. Suponha que queremos exportar uma lista de trabalhos de agente do SQL Server. Eu criei um script chamado Sqljobs.sql que recupera a lista de trabalhos SQL. O script contém o seguinte comando t-sql:
use (msdb)
go
select name, description,date_created from SysjobsPara executar o script, executo o seguinte comando no PowerShell para o SQL Server.
invoke-sqlcmd -inputfile "D:\Scripts\SQLJobs.sql" | Out-File -FilePath "D:\Scripts\SQLJobs_List.txt"Depois que o comando é concluído, abro o arquivo de saída, que parece a seguir.
Técnicas avançadas de SQLCMD
Aqui estão algumas técnicas avançadas que podem ajudá -lo a usar o SQLCMD com mais eficiência. Vou explicá -los com exemplos simples.
Exemplo 1: mostre mensagens de erro de acordo com o nível de gravidade do erro
Este exemplo mostra como exibir uma mensagem de erro de acordo com seu nível de gravidade. Este método pode ser usado adicionando o -m opção. Suponha que você esteja executando uma consulta selecionada contra um objeto de banco de dados inexistente. O comando retornará “Objeto inválido”e o nível de gravidade desse erro é 16. Veja a seguinte captura de tela.
Vamos dar uma olhada em um erro que tem um nível de gravidade de 15 – um erro de sintaxe
Como você pode ver na captura de tela acima, a gravidade do erro é 15, portanto, o SQLCMD não mostrou nenhum erro.
Exemplo 2: Saia da sessão SQLCMD quando ocorrer um erro
Este exemplo mostra como sair da sua sessão SQLCMD quando um comando ou consulta encontra um erro. Para fazer isso, você deve especificar o -b opção. Suponha que você queira sair do SQLCMD quando a consulta encontrar um “O banco de dados não existe” erro.
Exemplo 3: Aceite a entrada do usuário
Este exemplo mostra como aceitar a entrada do usuário ao executar um script T-SQL. Isso envolve variáveis de script no SQLCMD. Para demonstrar isso, criei um roteiro que preenche o nome formal do país. O script usa o WideWorldImporters banco de dados e o Application.Countries mesa. O conteúdo do script é o seguinte:
use (WideWorldImporters)
Go
select CountryName, FormalName from application.countries where CountryName=$(CountryName)
GoAgora eu salvo o script e o executo usando o seguinte comando sqlcmd:
sqlcmd -S Nisarg-PC -v CountryName="India" -i D:\Scripts\Asia_Countries.sqlAqui está a saída.
Como você pode ver, a consulta retornou o nome formal Índia.
Conclusão
Neste artigo, você aprendeu sobre o comando sqlcmd e como usá -lo com vários exemplos. O SQLCMD é uma ferramenta poderosa que pode ajudá -lo a executar scripts, exportar sua saída para vários arquivos e administrar o SQL Server. Você também pode usar o DAC (conexão de administrador dedicada), que ajuda a acessar servidores de banco de dados danificados ou corrompidos.
Por fim, você sempre pode ligar os recursos de estoque do SSMS com conclusão e formatação inteligentes de código, controle de origem, teste de unidade, automação da linha de comando e muitas outras coisas úteis disponíveis em pacotes como como Ferramentas SQL DBFORGE.
Novos usuários podem dar um test drive gratuitamente por 30 dias. Depois de instalar o pacote, todos os complementos estarão convenientemente disponíveis no menu SSMS e no Object Explorer. Eles me economizam tanto tempo que não posso deixar de recomendá -los.
