This example demonstrates the way to create a variable and make use of it for multiple purpose.
Actually in this example we would see how to create a variable and append strings into it. Lets create two Database with slight difference in the name. For example, DB1 and DB2. Then create a table in each DB and populate few records into it.
---Code snippet which needs to be run in Mgmt Studio starts here---
Create Database DB1
Create table t1 (a int)
insert into t1 values (1)
insert into t1 values (2)
insert into t1 values (3)
Create database DB2
Create table t2 (Num int)
insert into t2 values (4)
insert into t2 values (5)
insert into t2 values (6)
--Code snippet to be run in Mgmt studio ends here---
Now open command prompt and connect into the DB.
Step 1: SQLCMD -U sa -P hot -S VADIVEL
Step 2: Press Enter
Now lets create a variable by name "dbname" and assign 'DB' as the string to it.
Step 3: :setvar dbname DB
Now lets make use of this variable and list out all records from table t1
Step 4: use $(dbname)1
Step 5: go
Step 6: select * from t1
Step 7: go
If you see along with the variable I have appended the value 1. So that it would take it as 'DB1'. Hope I have made the point!
Now lets make use of this variable and list out all records from table t2 which exists within DB2 database
Step 8: use $(dbname)2
Step 9: go
Step 10: Select * from t2
Step 11: go