Wednesday, September 26, 2007

SQLCMD -- Part VI (Scripting Variables)

To list all available SQLCMD Scripting variables, do the following:

Step 1: Go to DOS prompt and open up SQLCMD
Step 2: type :listvar which would list all SQLCMD scripting variables.

In the screenshot you can see that the SQLCMDEditor and SQLCMDINI variable which we overwrote in the previous posts here and here are displayed.

Almost all of the other variables are self-explanatory :)

Creating our own local variables:

:setvar DB1 testBed
:setvar DB2 Adventureworks

use $(DB1)

Select getdate();

use $(DB2)

select top 5 city from person.address

Point to note:

1. If you execute :listvar again you can find these newly created variables getting listed there now. But please note that these local variables would be alive only till the life of the current session. i.e., If you exit out of SQLCMD once and come back and type :listvar these variables would be missing there.

2. Always the variables which we create will be in Uppercase only. Just try creating something like this:

:setvar NaMe Vadivel

You can see the variable got created as NAME in uppercase.

Check out these other options to get your hands dirty:

1. :help --> This would list out all the commands available for SQLCMD

2. -o --> this is for specifying output file path
3. :listvar --> I have talked about few of the variables in it. Just tryout the other variables yourself.

Hope this series of 6 posts would have given a general understanding of SQLCMD for the readers!

No comments: