By switching on SQLCMD mode in SQL Server Management Studio you can add some useful extra scripting functionality that is not
available with T-SQL. One particularly useful feature is the ability to switch between different SQL Servers within a query window. Without
SQLCMD the usual way to do this is to bring up the ‘Connect to Database Engine’ window (for instance by right clicking and selecting
‘Change Connection’). This can become quite tedious, especially when there are several SQL Servers to connect to. Once SQLCMD is enabled
this can be scripted within the query window.
Enabling SQLCMD Mode
To enable SQLCMD mode (it is off by default) simply select ‘SQLCMD Mode’ from the Query menu in SSMS, as below :
Using SQLCMD to Switch servers in Management Studio
All SQLCMD commands start with a colon, and the command to change servers is ‘CONNECT’. To change to the local server using
localhost use the following command in a database query window :
Note that if SSMS intellisense does not shade the background to be grey then the chances are that you’ve not switched on SQLCMD mode.
Alternatively to connect to a specific server (in this case SQLSERVER1) use :
Or for a non-default instance (in this case INSTANCE1 on server SQLSERVER1) :
The above commands assume that you are using Windows Authentication, if you want to use SQL Server Authentication and specify a login and password, then you can use the following syntax :
:CONNECT SQLSERVER1 -U MyLoginName -P MyPassword
A Practical Example
By way of example the above technique can be used to get a list of all databases on each SQL Server instance.
In my case I have three SQL servers and these are named SQLMATTERS1, SQLMATTERS2 and SQLMATTERS3. I want to connect to each server using
Windows Authentication and run the following query :
SELECT * FROM sys.databases
I can do this in SQL Server Management Studio using the techniques described above. The SQL and results are shown in the
screenshot below, which is hopefully self explanatory :
A Final Note - Using the ‘GO’ Batch Separator
When connecting to multiple servers it’s important to terminate each set of statements to be run with a ‘GO’
batch terminator before switching server with the
CONNECT statement. If you don’t then the query may not be run against the correct server.