次の方法で共有

sqlcmd - スクリプト変数を使う

  • [アーティクル]

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric SQL Database

スクリプトで使用される変数は、スクリプト変数と呼ばれます。 スクリプト変数を使用すると、1 つのスクリプトを複数のシナリオで使用できます。 たとえば、1 つのスクリプトを複数のサーバーに対して実行する場合、各サーバー用にスクリプトを変更するのではなく、サーバー名にスクリプト変数を使用することができます。 サーバー名をスクリプト変数で指定することで、同じスクリプトを複数のサーバーで実行することができるようになります。

スクリプト変数は、setvar コマンドを使用して明示的に定義するか、または sqlcmd -v オプションを使用して暗黙的に定義できます。

この記事では、SET を使って Cmd.exe コマンド プロンプトで環境変数を定義する例も紹介しています。

setvar コマンドでスクリプト変数を設定する

setvar コマンドは、スクリプト変数を定義するのに使用します。 setvar コマンドを使用して定義されている変数は、内部的に格納されます。 スクリプト変数は、SET を使用してコマンド プロンプトで定義されている環境変数と混同しないようにする必要があります。 環境変数でもなく setvar コマンドを使用して定義したものでもない変数をスクリプト内で参照していると、エラー メッセージが表示され、スクリプトの実行は停止されます。 詳細については、「sqlcmd」の -b オプションの説明を参照してください。

変数の優先順位 (低から高)

複数の種類の変数に同じ名前が付いている場合、優先順位の最も高い変数が使用されます。

  1. システム レベル環境変数
  2. ユーザー レベル環境変数
  3. sqlcmd の起動前にコマンド プロンプトで設定されたコマンド シェル (SET X=Y)
  4. sqlcmd -v X=Y
  5. :Setvar X Y

Note

環境変数を表示するには、[コントロール パネル] の [システム] アイコンを開き、[詳細設定] タブを選びます。

スクリプト変数の暗黙的な設定

関連する sqlcmd 変数を含むオプションを指定して sqlcmd を起動すると、 sqlcmd 変数には、そのオプションを使用して指定されている値が暗黙的に設定されます。 次の例では、 sqlcmd が -l オプションを指定して起動されています。 これにより、SQLLOGINTIMEOUT 変数が暗黙的に設定されます。

sqlcmd -l 60

-v オプションを使用して、スクリプト内に存在するスクリプト変数を設定することもできます。 次のスクリプト (ファイル名は testscript.sql) では、 ColumnName がスクリプト変数です。

USE AdventureWorks2022;

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

その後、 -v オプションを使用して、取得する列の名前を指定できます。

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

同じスクリプトを使用して別の列を取得するには、 ColumnName スクリプト変数の値を変更します。

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

スクリプト変数の名前と値に関するガイドライン

スクリプト変数に名前を指定する場合は、次のガイドラインを考慮してください。

  • 変数名には空白文字または引用符を使用できません。

  • 変数名には、 $(var) のような変数式と同じ形式を使用することはできません。

  • スクリプト変数では、大文字と小文字が区別されません。

    Note

    sqlcmd 環境変数に値が割り当てられていない場合、この変数は削除されます。 値を指定せずに :setvar VarName を使用すると、変数がクリアされます。

スクリプト変数に値を指定する場合は、次のガイドラインを考慮してください。

  • setvar または -v オプションを使用して定義された変数値は、空白を含む文字列値の場合に引用符で囲む必要があります。
  • 変数値に引用符が使用されている場合は、その引用符をエスケープする必要があります。 たとえば、setvar MyVar "spac""e"のように指定します。

Cmd.exe の SET による変数の値と名前に関するガイドライン

SET を使用して定義された変数は、cmd.exe 環境で使用されるため、sqlcmd で参照できます。 次のガイドラインを考慮してください。

  • 変数名には空白文字または引用符を使用できません。
  • 変数値には空白文字または引用符を使用できます。

sqlcmd スクリプト変数

sqlcmd で定義される変数はスクリプト変数と呼ばれます。 次の表は、 sqlcmd スクリプト変数の一覧です。

変数 関連するオプション R/W 既定値
SQLCMDUSER 1 -U R 2 ""
SQLCMDPASSWORD 1 -P -- ""
SQLCMDSERVER 1 S- R 2 "DefaultLocalInstance"
SQLCMDWORKSTATION -H R 2 "ComputerName"
SQLCMDDBNAME -d R 2 ""
SQLCMDLOGINTIMEOUT -l R/W 3 "8" (秒)
SQLCMDSTATTIMEOUT -t R/W 3 "0" = 無制限に待機
SQLCMDHEADERS -h R/W 3 "0"
SQLCMDCOLSEP -s R/W 3 " "
SQLCMDCOLWIDTH -w R/W 3 "0"
SQLCMDPACKETSIZE -a R 2 "4096"
SQLCMDERRORLEVEL -m R/W 3 "0"
SQLCMDMAXVARTYPEWIDTH -y R/W 3 "256"
SQLCMDMAXFIXEDTYPEWIDTH -y R/W 3 "0" = 無制限
SQLCMDEDITOR R/W 3 "edit.com"
SQLCMDINI R 2 ""

1 SQLCMDUSER、SQLCMDPASSWORD および SQLCMDSERVER は、:Connect が使用されるときに設定されます。

2 R は、その値がプログラムの初期化時に一度だけ設定できることを示します。

3 R/W は、setvar コマンドを使用して値をリセットできること、および後続のコマンドで新しい値が使用されることを示します。

例

A. スクリプトで setvar コマンドを使う

多くの sqlcmd オプションは、スクリプトで setvar コマンドを使用して制御できます。 次の例では、スクリプト test.sql が作成されます。このスクリプトでは、変数 SQLCMDLOGINTIMEOUT が 60 秒に設定され、別のスクリプト変数 serverが testserverに設定されています。 test.sqlのコードを次に示します。

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

USE AdventureWorks2022;

SELECT FirstName, LastName
FROM Person.Person;

このスクリプトは、sqlcmd を使用して呼び出されます。

sqlcmd -i c:\test.sql

B. setvar コマンドを対話的に使う

次の例では、 setvar コマンドを使用してスクリプト変数を対話的に設定する方法を示します。

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

結果セットは次のとおりです。

Changed database context to 'AdventureWorks2022'
1>

C: sqlcmd 内でコマンド プロンプト環境変数を使う

次の例では、4 つの環境変数 are を設定した後、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. sqlcmd 内でユーザーレベル環境変数を使う

次の例では、ユーザーレベル環境変数 %Temp% をコマンド プロンプトで設定し、sqlcmd 入力ファイルに渡します。 ユーザーレベル環境変数を取得するには、 [コントロール パネル] の [システム] をダブルクリックします。 [詳細] タブ、[環境変数] を順に選びます。

入力ファイル C:\testscript.txtのコードを次に示します。

:OUT $(MyTempDirectory)
USE AdventureWorks2022;

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

コマンド プロンプトで、次のコードを入力します。

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

次の結果が出力ファイル C:\Documents and Settings\<user>\Local Settings\Temp\output.txt に送信されます。

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

(4 rows affected)

E. スタートアップ スクリプトを使う

sqlcmd スタートアップ スクリプトは、 sqlcmd の起動時に実行されます。 次の例では、環境変数 SQLCMDINIが設定されます。 以下の内容を次に示します: 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

次のコードにより、 init.sql の起動時に sqlcmd ファイルが呼び出されます。

SET sqlcmdini=c:\init.sql
sqlcmd

出力結果は次のとおりです。

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

Note

-X オプションを使用すると、スタートアップ スクリプト機能が無効になります。

F. 変数の拡張

次の例では、 sqlcmd 変数の形式でデータを使用する方法を示します。

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

Col1 の dbo.VariableTest に、値 $(tablename)を含む 1 行を挿入します。

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

sqlcmd プロンプトで、$(tablename) と等しい変数が設定されていない場合、次のステートメントでは行が返され、また "'tablename' スクリプト変数が定義されていません" というメッセージも返されます。既定では、sqlcmd フラグ -b は設定されていません。 -b が設定されている場合、sqlcmd は "変数が定義されていません" というエラーの後に終了します。

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

結果セットは次のとおりです。

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

変数 MyVar が $(tablename)に設定されている場合は次のようになります。

6> :setvar MyVar $(tablename)

次のステートメントでは、行だけでなく、"'tablename' スクリプト変数が定義されていません。" というメッセージも返されます。

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

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

次のステートメントでは行が返されます。

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

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

次の手順