SQL SERVER MANAGEMENT STUDIO -巧用QUERY menu

最新推荐文章于 2021-01-21 11:22:50 发布

dbLenis

最新推荐文章于 2021-01-21 11:22:50 发布

阅读量886

点赞数

分类专栏： sql server 文章标签： sql server query menu 数据库 server ssms

sql server 专栏收录该内容

68 篇文章 2 订阅

订阅专栏

在一个T-SQL Script里面，连接不同的sql server数据库，无需手工连接和频繁切换sql server

以下是网络实例：

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

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.

大意是，如果需要copy另一个sql server数据库中的stored procedure,并且这两个数据库不在同一个instance下面，那么需要手工拿到这个stored procedure，然后切换到需要创建的instance database中，执行create procedure.有没有办法可以直接从原有的sql server中，生成这个sp script之后，直接用代码连接到需要重新生成sp的数据库下面，执行create procedure呢？这样就免去手工切换sql server instance的麻烦了。答案有很多种，解释下自己不会的：

Here's an option:

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]]

使用:connect去连sql server instance。在query menu里面，启动SQLCMD mode

copy from : http://stackoverflow.com/questions/125457/what-is-the-t-sql-syntax-to-connect-to-another-sql-server