tsql - What is the T-SQL syntax to connect to another SQL Server?

Question

If I need to copy a stored procedure (SP) from one SQL Server to another I right click on the SP in SSMS and select Script Stored Procedure as > CREATE to > New Query Editor Window. I then change the connection by right clicking on that window and selecting Connection > Change Connection... and then selecting the new server and F5 to run the create on the new server.

So my question is "What is the T-SQL syntax to connect to another SQL Server?" so that I can just paste that in the top of the create script and F5 to run it and it would switch to the new server and run the create script.

While typing the question I realized that if I gave you the back ground to what I'm trying to do that you might come up with a faster and better way from me to accomplish this.

Answer 1

In SQL Server Management Studio, turn on SQLCMD mode from the Query menu. Then at the top of your script, type in the command below

:Connect server_name[instance_name] [-l timeout] [-U user_name [-P password]

If you are connecting to multiple servers, be sure to insert GO between connections; otherwise your T-SQL won't execute on the server you're thinking it will.