sqlcmd – Usar com variáveis de script

Artigo
11/19/2024

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics PDW (Analytics Platform System) Banco de Dados SQL no Microsoft Fabric

As variáveis usadas em scripts normalmente são chamadas de variáveis de script. As variáveis de script habilitam um script ser usado em vários cenários. Por exemplo, se você desejar executar um script em vários servidores, em vez de modificar o script para cada servidor, pode usar uma variável de script para o nome do servidor. Alterando o nome do servidor fornecido para a variável de script, o mesmo script pode ser executado em diferentes servidores.

As variáveis de script podem ser definidas explicitamente usando o comando setvar ou implicitamente usando a opção sqlcmd -v.

Este artigo também inclui exemplos que definem variáveis ambientais no prompt de comando Cmd.exe usando SET.

Definir variáveis de script usando o comando setvar

O comando setvar é usado para definir variáveis de script. Variáveis definidas usando o comando setvar são armazenadas internamente. Variáveis de script não devem ser confundidas com variáveis de ambiente, que são definidas no prompt de comando usando SET. Se um script fizer referência a uma variável que não é uma variável de ambiente ou não foi definida usando setvar, uma mensagem de erro será retornada e a execução do script é interrompida. Para obter mais informações, consulte a opção -b em sqlcmd.

Precedência de variável (baixa para alta)

Se mais de um tipo de variável tiver o mesmo nome, a variável com precedência mais alta será usada.

Variáveis ambientais do nível de sistema
Variáveis ambientais do nível de usuário
Shell de comando (SET X=Y) definido no prompt de comando antes de iniciar o sqlcmd
sqlcmd -v X=Y
:Setvar X Y

Observação

Para exibir as variáveis ambientais, no Painel de Controle, abra Sistemae selecione a guia Avançado.

Definindo implicitamente variáveis de script

Ao iniciar o sqlcmd com uma opção que contém uma variável sqlcmd relacionada, a variável sqlcmd será implicitamente definida com o valor especificado com a opção. No exemplo a seguir, o sqlcmd é iniciado com a opção -l . Isto define implicitamente a variável SQLLOGINTIMEOUT.

sqlcmd -l 60

Também é possível usar a opção -v para definir uma variável de script existente em um script. O script a seguir (o nome de arquivo é testscript.sql), ColumnName é uma variável de script.

USE AdventureWorks2022;

SELECT x.$(ColumnName)
FROM Person.Person x
WHERE x.BusinessEntityID < 5;

Assim você pode especificar o nome da coluna que deseja que retorne usando a opção -v :

sqlcmd -v ColumnName ="FirstName" -i c:\testscript.sql

Para retornar uma coluna diferente usando o mesmo script, altere o valor da variável de script ColumnName .

sqlcmd -v ColumnName ="LastName" -i c:\testscript.sql

Diretrizes para nomes e valores de variáveis de script

Considere as seguintes diretrizes ao nomear variáveis de script:

Os nomes das variáveis não devem conter caracteres de espaços em brancos ou aspas.
Os nomes das variáveis não devem ter a mesma forma que uma expressão variável, como $(var) .
Variáveis de script não diferenciam maiúsculas de minúsculas.

Observação

Se nenhum valor for atribuído a uma variável de ambiente sqlcmd , a variável será removida. Usar :setvar VarName sem um valor limpa a variável.

Considere as seguintes diretrizes ao especificar valores para variáveis de script:

Valores de variáveis definidos usando setvar ou a opção -v deverão ser mantidos entre aspas se o valor da cadeia contiver espaços.
Se houver aspas no valor da variável, elas devem ser substituídas. Por exemplo:setvar MyVar "spac""e".

Diretrizes para nomes e valores da variável SET de Cmd.exe

Variáveis definidas usando SET fazem parte do ambiente de Cmd.exe e podem ser referenciadas por sqlcmd. Considere as seguintes diretrizes:

Os nomes das variáveis não devem conter caracteres de espaços em brancos ou aspas.
Valores de variáveis podem conter espaços ou aspas.

Variáveis de script do sqlcmd

Variáveis definidas por sqlcmd são conhecidas como variáveis de script. A tabela a seguir lista variáveis de script do sqlcmd .

Variável	Opção relacionada	R/W	Padrão
SQLCMDUSER ¹	-U	R ²	""
SQLCMDPASSWORD ¹	-P	--	""
SQLCMDSERVER ¹	-S	R ²	"DefaultLocalInstance"
SQLCMDWORKSTATION	-H	R ²	"ComputerName"
SQLCMDDBNAME	-d	R ²	""
SQLCMDLOGINTIMEOUT	-l	R/W ³	"8" (segundos)
SQLCMDSTATTIMEOUT	-T	R/W ³	"0" = espere indefinidamente
SQLCMDHEADERS	-h	R/W ³	"0"
SQLCMDCOLSEP	-S	R/W ³	" "
SQLCMDCOLWIDTH	w-	R/W ³	"0"
SQLCMDPACKETSIZE	-a	R ²	"4096"
SQLCMDERRORLEVEL	-M	R/W ³	"0"
SQLCMDMAXVARTYPEWIDTH	-y	R/W ³	"256"
SQLCMDMAXFIXEDTYPEWIDTH	-y	R/W ³	"0" = ilimitado
SQLCMDEDITOR		R/W ³	"edit.com"
SQLCMDINI		R ²	""

¹ SQLCMDUSER, SQLCMDPASSWORD e SQLCMDSERVER são definidos ao usar :Connect.

² R indica que o valor pode ser definido apenas uma vez durante a inicialização do programa.

³ R/W indica que o valor pode ser redefinido usando o comando setvar e que comandos subsequentes usarão o novo valor.

Exemplos

a. Usar o comando setvar em um script

Muitas opções do sqlcmd podem ser controladas em um script usando o comando setvar . No exemplo a seguir, o script test.sql é criado e nele a variável SQLCMDLOGINTIMEOUT é definida como 60 segundos e outra variável de script, server, é definida como testserver. O código a seguir está em test.sql.

:setvar SQLCMDLOGINTIMEOUT 60
:setvar server "testserver"
:connect $(server) -l $(SQLCMDLOGINTIMEOUT)

USE AdventureWorks2022;

SELECT FirstName, LastName
FROM Person.Person;

O script é então chamado usando o sqlcmd:

sqlcmd -i c:\test.sql

B. Usar o comando setvar interativamente

O exemplo a seguir mostra como definir interativamente uma variável de script usando o comando setvar .

sqlcmd
:setvar MYDATABASE AdventureWorks2022
USE $(MYDATABASE);
GO

Veja a seguir o conjunto de resultados.

Changed database context to 'AdventureWorks2022'
1>

C. Usar variáveis de ambiente de prompt de comando no sqlcmd

No exemplo a seguir, quatro variáveis de ambiente are são definidas e depois chamadas a partir do sqlcmd.

SET tablename=Person.Person
SET col1=FirstName
SET col2=LastName
SET title=Ms.
sqlcmd -d AdventureWorks2022
1> SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name
2> FROM $(tablename)
3> WHERE Title ='$(title)'
4> GO

D. Usar variáveis de ambiente em nível de usuário no sqlcmd

No exemplo a seguir, a variável de ambiente de nível de usuário %Temp% está definida no prompt de comando e foi passada para o arquivo de entrada do sqlcmd. Para obter a variável de ambiente de nível de usuário: em Painel de Controle, clique duas vezes em Sistema. Selecione a guia Avançar e, em seguida, Variáveis de Ambiente.

O código a seguir está no arquivo de entrada C:\testscript.txt:

:OUT $(MyTempDirectory)
USE AdventureWorks2022;

SELECT FirstName
FROM AdventureWorks2022.Person.Person
WHERE BusinessEntityID < 5;

O código a seguir é inserido no prompt de comando:

SET MyTempDirectory=%Temp%\output.txt
sqlcmd -i C:\testscript.txt

O resultado a seguir é enviado ao arquivo de saída C:\Documents and Settings\<user>\Local Settings\Temp\output.txt.

Changed database context to 'AdventureWorks2022'.
FirstName
--------------------------------------------------
Gustavo
Catherine
Kim
Humberto

(4 rows affected)

E. Usar um script de inicialização

Um script de inicialização sqlcmd é executado quando sqlcmd é iniciado. O exemplo a seguir define a variável de ambiente de SQLCMDINI. Este é o conteúdo de init.sql.

SET NOCOUNT ON
GO

DECLARE @nt_username nvarchar(128)
SET @nt_username = (SELECT rtrim(convert(nvarchar(128), nt_username))
FROM sys.dm_exec_sessions WHERE spid = @@SPID)
SELECT  @nt_username + ' is connected to ' +
rtrim(CONVERT(nvarchar(20), SERVERPROPERTY('servername'))) +
' (' +`
rtrim(CONVERT(nvarchar(20), SERVERPROPERTY('productversion'))) +
')'
:setvar SQLCMDMAXFIXEDTYPEWIDTH 100
SET NOCOUNT OFF
GO

:setvar SQLCMDMAXFIXEDTYPEWIDTH

Isto chama o arquivo init.sql quando o sqlcmd é iniciado.

SET sqlcmdini=c:\init.sql
sqlcmd

Esta é a saída.

1> <user> is connected to <server> (9.00.2047.00)

Observação

A opção -X desabilita o recurso de script de inicialização.

F. Expansão de variável

O exemplo a seguir mostra o trabalho com dados no formato de uma variável sqlcmd .

USE AdventureWorks2022;
GO
CREATE TABLE AdventureWorks2022.dbo.VariableTest (Col1 NVARCHAR(50));
GO

Insira uma linha em Col1 de dbo.VariableTest que contém o valor $(tablename).

INSERT INTO AdventureWorks2022.dbo.VariableTest (Col1)
VALUES ('$(tablename)');
GO

No prompt sqlcmd, quando nenhuma variável é definida como igual a $(tablename), as instruções a seguir retornam a linha e a mensagem "variável de script 'tablename' não definida". Por padrão, o sinalizador -b de sqlcmd não está definido. Se -b estiver definido, o sqlcmd será encerrado após o erro "variável não definida" aparecer.

sqlcmd
1> SELECT Col1 FROM dbo.VariableTest WHERE Col1 = '$(tablename)';
2> GO
3> SELECT Col1 FROM dbo.VariableTest WHERE Col1 = N'$(tablename)';
4> GO

Veja a seguir o conjunto de resultados.

1> Col1
2> ------------------
3> $(tablename)
4>
5> (1 rows affected)

Dado que a variável MyVar foi definida como $(tablename).

6> :setvar MyVar $(tablename)

Estas instruções retornam a linha e também a mensagem "variável de script 'tablename' não definida".

6> SELECT Col1 FROM dbo.VariableTest WHERE Col1 = '$(tablename)';
7> GO

1> SELECT Col1 FROM dbo.VariableTest WHERE Col1 = N'$(tablename)';
2> GO

Estas instruções retornam a linha.

1> SELECT Col1 FROM dbo.VariableTest WHERE Col1 = '$(MyVar)';
2> GO

1> SELECT Col1 FROM dbo.VariableTest WHERE Col1 = N'$(MyVar)';
2> GO

Partilhar via