Running Query in SQLCMD mode in SSMS

Today I would like to talk about another easily overlooked but very useful feature in SQL Server Management Studio, which is running query in SQLCMD mode. This mode allows you to embed SQLCMD comands into your T-SQL script and execute it by using the SSMS. The most powerful use of this feature is you can run one script in multiple server instances, by using the :CONNECT SQLCMD command to connect to different instances. For example, you need to check the synchronization state of all databases in all production server instances, rather than connecting the server instances one by one in SSMS and checking them by Availability Group Dashboard, you will be more comfortable to just execute one script file once like below:

:CONNECT (local)\TARGETSVR1
SELECT R.replica_server_name, D.synchronization_state_desc, B.[name] AS DatabaseName FROM sys.dm_hadr_availability_replica_cluster_states R JOIN sys.dm_hadr_database_replica_states D ON R.replica_id = D.replica_id JOIN sys.databases B ON D.database_id = B.database_id WHERE replica_server_name = @@SERVERNAME;
GO
:CONNECT (local)\TARGETSVR2
SELECT R.replica_server_name, D.synchronization_state_desc, B.[name] AS DatabaseName FROM sys.dm_hadr_availability_replica_cluster_states R JOIN sys.dm_hadr_database_replica_states D ON R.replica_id = D.replica_id JOIN sys.databases B ON D.database_id = B.database_id WHERE replica_server_name = @@SERVERNAME;
GO

Before running the above script, you should enable SQLCMD mode for the opening query editor:

Remind that you must type the GO command before each :CONNECT command, in order to seperate each batch to be executed on different server instances, otherwise all batches will be executed on the lowest server instance in your script, which will be a mistake, e.g.