In SQL Server, like "use <<DatabaseName>>", how to "use <<ServerName>>" Query command

Question

In SQL Server, like use <<DatabaseName>>, how to give use <<ServerName>> query command?

Right Click | Change Connection must be associated to some command. Isn't it. Just curious..

Answer 1

If you are attempting to fully switch connections¹ within a single script and work with a new connection as opposed to sharing data between connections, then you can do this with SQLCMD mode. This ability / feature is specific to SQL Server Management Studio (SSMS) and the SQLCMD.EXE command-line utility as SQLCMD mode is directives to the client tool (either SSMS or SQLCMD.EXE) and not something that the database engine will be executing (or will even see or ever know about).

You can enable SQLCMD mode in SSMS on a per-session basis by going to the Query menu and selecting SQLCMD Mode. Once SQLCMD mode is enabled, you can change connections be using the :connect command:

SELECT @@SERVERNAME AS [ServerName], DB_NAME() AS [DbName]
GO
:connect DifferentServerName
SELECT @@SERVERNAME AS [ServerName], DB_NAME() AS [DbName]

Notes:

• SQLCMD directives are processed at the beginning of each batch (since the entire batch is then sent to SQL Server for execution). Without using GO to separate the batches in the above example, the :connect command will take effect before the first SELECT. Comment out the GO and run again to see the effect.
• When using SQLCMD Mode, Intellisense will not work.
• SQLCMD Mode cannot be toggled on or off programmatically, but you can have SQLCMD Mode enabled for all new query windows by going to Tools | Options | Query Execution and checking the box for "By default, open new queries in SQLCMD mode".
• There is no SQLCMD mode for the SQLCMD.EXE command-line utility; these commands always work in that tool.

¹ Changing the connection logs you out of the current connection. This will drop temporary objects, rollback uncommitted transactions, etc., and local variables will obviously be out of scope (i.e. cannot cross connection boundaries). If statements executed on both servers need to share any info or objects, then you will need to create a Linked Server on one of them and use that Linked Server to connect to the other server on the current connection. Variables and temp objects still can't transfer between them, but you would then at least have the ability to construct Dynamic SQL containing that info to then execute on the remote server, and/or use the local resources in queries that specify four-part names for the remote objects.