sqlcmd でのスクリプト変数の使用
スクリプトで使用される変数は、スクリプト変数と呼ばれます。スクリプト変数を使用すると、1 つのスクリプトを複数のシナリオで使用できます。たとえば、1 つのスクリプトを複数のサーバーに対して実行する場合、各サーバー用にスクリプトを変更するのではなく、サーバー名にスクリプト変数を使用することができます。サーバー名をスクリプト変数で指定することで、同じスクリプトを複数のサーバーで実行することができるようになります。
スクリプト変数は、setvar コマンドを使用して明示的に定義するか、または sqlcmd-v オプションを使用して暗黙的に定義できます。
このトピックでは、SET を使用して Cmd.exe コマンド プロンプトで環境変数を定義する例も紹介しています。
setvar コマンドを使用したスクリプト変数の設定
setvar コマンドは、スクリプト変数を定義するのに使用します。setvar コマンドを使用して定義されている変数は、内部的に格納されます。スクリプト変数は、SET を使用してコマンド プロンプトで定義されている環境変数と混同しないようにする必要があります。環境変数でもなく setvar コマンドを使用して定義したものでもない変数をスクリプト内で参照していると、エラー メッセージが表示され、スクリプトの実行は停止されます。詳細については、「sqlcmd ユーティリティ」の -b オプションの説明を参照してください。
変数の優先順位 (低から高)
複数の種類の変数に同じ名前が付いている場合、優先順位の最も高い変数が使用されます。
システム レベル環境変数
ユーザー レベル環境変数
sqlcmd の起動前にコマンド プロンプトで設定されたコマンド シェル (SET X=Y)
sqlcmd-v X=Y
:Setvar X Y
.gif "注意") 注意 |
|---|
環境変数を表示するには、[コントロール パネル] の [システム] アイコンを開き、[詳細設定] タブをクリックします。 |
スクリプト変数の暗黙的な設定
関連する sqlcmd 変数を含むオプションを指定して sqlcmd を起動すると、sqlcmd 変数には、そのオプションを使用して指定されている値が暗黙的に設定されます。次の例では、sqlcmd が -l オプションを指定して起動されています。このコマンドを実行すると、SQLLOGINTIMEOUT 変数が暗黙的に設定されます。
c:\> sqlcmd -l 60
-v オプションを使用して、スクリプト内に存在するスクリプト変数を設定することもできます。次のスクリプト (ファイル名は testscript.sql) では、ColumnName がスクリプト変数です。
USE AdventureWorks;
SELECT x.$(ColumnName)
FROM Person.Contact x
WHERE c.ContactID < 5;
その後、-v オプションを使用して、取得する列の名前を指定できます。
sqlcmd -v ColumnName ="FirstName" -i c:\testscript.sql
同じスクリプトを使用して別の列を取得するには、ColumnName スクリプト変数の値を変更します。
sqlcmd -v ColumnName ="LastName" -i c:\testscript.sql
スクリプト変数の名前と値に関するガイドライン
スクリプト変数に名前を指定する場合は、次のガイドラインを考慮してください。
変数名には空白文字または引用符を使用できません。
変数名には、$(var) のような変数式と同じ形式を使用することはできません。
スクリプト変数では、大文字と小文字が区別されません。
注意sqlcmd 環境変数に値が割り当てられていない場合、この環境変数は削除されます。値を指定せずに :setvar VarName を使用すると、変数は削除されます。
スクリプト変数に値を指定する場合は、次のガイドラインを考慮してください。
setvar または -v オプションを使用して定義する変数値は、空白を含む文字列値の場合に引用符で囲む必要があります。
変数値に引用符が使用されている場合は、その引用符をエスケープする必要があります。たとえば、setvar MyVar "spac""e" のように指定します。
Cmd.exe の SET による変数の値と名前に関するガイドライン
SET を使用して定義される変数は、Cmd.exe 環境で使用されるため、sqlcmd で参照できます。次のガイドラインを考慮してください。
変数名には空白文字または引用符を使用できません。
変数値には空白文字または引用符を使用できます。
sqlcmd スクリプト変数
sqlcmd で定義される変数はスクリプト変数と呼ばれます。次の表は、sqlcmd スクリプト変数の一覧です。
変数 |
関連するオプション |
R/W |
既定値 |
|---|---|---|---|
SQLCMDUSER* |
-U |
R |
"" |
SQLCMDPASSWORD* |
-P |
-- |
"" |
SQLCMDSERVER* |
-S |
R |
"DefaultLocalInstance" |
SQLCMDWORKSTATION |
-H |
R |
"ComputerName" |
SQLCMDDBNAME |
-d |
R |
"" |
SQLCMDLOGINTIMEOUT |
-l |
R/W |
"8" (秒) |
SQLCMDSTATTIMEOUT |
-t |
R/W |
"0" = 無制限に待機 |
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" = 無制限 |
SQLCMDEDITOR |
R/W |
"edit.com" |
|
SQLCMDINI |
R |
"" |
* SQLCMDUSER、SQLCMDPASSWORD および SQLCMDSERVER は、:Connect が使用されるときに設定されます。
R は、その値がプログラムの初期化時に一度だけ設定できることを示します。
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 AdventureWorks;
SELECT FirstName, LastName
FROM Person.Contact;
The script is then called by using sqlcmd:
sqlcmd -i c:\test.sql
B. setvar コマンドの対話的な使用
次の例では、setvar コマンドを使用してスクリプト変数を対話的に設定する方法を示します。
sqlcmd
:setvar MYDATABASE AdventureWorks
USE $(MYDATABASE);
GO
以下に結果セットを示します。
Changed database context to 'AdventureWorks'
1>
C. sqlcmd 内でのコマンド プロンプト環境変数の使用
次の例では、4 つの環境変数を設定した後、sqlcmd から呼び出します。
C:\>SET tablename=Person.Contact
C:\>SET col1=FirstName
C:\>SET col2=LastName
C:\>SET title=Ms.
C:\>sqlcmd -d AdventureWorks
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 AdventureWorks;
SELECT FirstName
FROM AdventureWorks.Person.Contact
WHERE ContactID < 5;
コマンド プロンプトで、次のコードを入力します。
C:\ >SET MyTempDirectory=%Temp%\output.txt
C:\ >sqlcmd -i C:\testscript.txt
出力ファイル C:\Documents and Settings\<user>\Local Settings\Temp\output.txt には、次の結果が送信されます。
Changed database context to 'AdventureWorks'.
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
次のコードにより、sqlcmd の起動時に init.sql ファイルが呼び出されます。
C:\> SET sqlcmdini=c:\init.sql
>1 Sqlcmd
出力結果は次のとおりです。
>1 < user > is connected to < server > (9.00.2047.00)
注 -X オプションを使用すると、スタートアップ スクリプト機能が無効になります。
F. 変数の拡張
次の例では、sqlcmd 変数の形式でデータを使用する方法を示します。
USE AdventureWorks;
CREATE TABLE AdventureWorks.dbo.VariableTest
(
Col1 nvarchar(50)
);
GO
dbo.VariableTest の Col1 に、値 $(tablename) を含む 1 行を挿入します。
INSERT INTO AdventureWorks.dbo.VariableTest(Col1)
VALUES('$(tablename)');
GO
sqlcmd プロンプトで、$(tablename) に変数を設定していない場合、次のステートメントでは行が返されます。
C:\> 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