如何确定已安装的SQL服务器实例及其版本?

I'm trying to determine what instances of sql server/sql express I have installed (either manually or programmatically) but all of the examples are telling me to run a SQL query to determine this which assumes I'm already connected to a particular instance.

我正在尝试确定我已经安装的sql server/sql express的实例(手动或编程)，但是所有的示例都告诉我运行一个sql查询来确定这个假设，假设我已经连接到一个特定的实例。

18 个解决方案

#1

At a command line:

在命令行:

SQLCMD -L

or

或

OSQL -L

(Note: must be a capital L)

(注:必须是大写L)

This will list all the sql servers installed on your network. There are configuration options you can set to prevent a SQL Server from showing in the list. To do this...

这将列出在您的网络上安装的所有sql服务器。您可以设置一些配置选项来防止SQL服务器在列表中显示。要做到这一点…

At command line:

在命令行:

svrnetcn

In the enabled protocols list, select 'TCP/IP', then click properties. There is a check box for 'Hide server'.

在启用的协议列表中，选择“TCP/IP”，然后单击“属性”。有一个“隐藏服务器”的复选框。

#2

You could query this registry value to get the SQL version directly:

您可以查询此注册表值以直接获得SQL版本:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup\CurrentVersion

Alternatively you can query your instance name and then use sqlcmd with your instance name that you would like:

或者，您可以查询您的实例名，然后使用您想要的实例名使用sqlcmd:

To see your instance name:

要查看您的实例名:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names

Then execute this:

然后执行:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

If you are using C++ you can use this code to get the registry information.

如果您使用c++，您可以使用此代码获取注册表信息。

#3

All of the instances installed should show up in the Services Snap-In in the Microsoft Management Console. To get the instance names, go to Start | Run | type Services.msc and look for all entries with "Sql Server (Instance Name)".

所有安装的实例都应该显示在Microsoft管理控制台的服务管理单元中。要获得实例名，可以启动|运行|类型服务。msc查找所有带有“Sql Server(实例名)”的条目。

#4

-- T-SQL Query to find list of Instances Installed on a machine

——T-SQL查询，以查找安装在机器上的实例列表。

DECLARE @GetInstances TABLE
( Value nvarchar(100),
 InstanceNames nvarchar(100),
 Data nvarchar(100))

Insert into @GetInstances
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances'

Select InstanceNames from @GetInstances 

#5

If you just want to see what's installed on the machine you're currently logged in to, I think the most straightforward manual process is to just open the SQL Server Configuration Manager (from the Start menu), which displays all the SQL Services (and only SQL services) on that hardware (running or not). This assumes SQL Server 2005, or greater; dotnetengineer's recommendation to use the Services Management Console will show you all services, and should always be available (if you're running earlier versions of SQL Server, for example).

如果你只是想看看你当前登录的机器上安装,我想最简单的手动过程是打开SQL Server Configuration Manager(从开始菜单),显示所有SQL服务(只有SQL服务)硬件(运行)。这假设SQL Server 2005或更大;dotnetengineer建议使用服务管理控制台将向您展示所有服务，并且应该始终是可用的(例如，如果您正在运行早期版本的SQL Server)。

If you're looking for a broader discovery process, however, you might consider third party tools such as SQLRecon and SQLPing, which will scan your network and build a report of all SQL Service instances found on any server to which they have access. It's been a while since I've used tools like this, but I was surprised at what they found (namely, a handful of instances that I didn't know existed). YMMV. You might Google for details, but I believe this page has the relevant downloads: http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx

但是，如果您正在寻找更广泛的发现过程，您可能会考虑诸如SQLRecon和SQLPing之类的第三方工具，它们将扫描您的网络，并构建一个报告，显示在任何服务器上找到的所有SQL服务实例。我使用过这样的工具已经有一段时间了，但是我对他们的发现感到惊讶(也就是我不知道的一些例子)。YMMV。您可能会对细节进行谷歌，但是我相信这个页面有相关的下载:http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx。

#6

I know this thread is a bit old, but I came across this thread before I found the answer I was looking for and thought I'd share. If you are using SQLExpress (or localdb) there is a simpler way to find your instance names. At a command line type:

我知道这条线有点旧，但在找到我要找的答案之前，我遇到了这个问题。如果您使用的是SQLExpress(或localdb)，有一种更简单的方法来查找实例名。在命令行类型:

> sqllocaldb i

This will list the instance names you have installed locally. So your full server name should include (localdb)\ in front of the instance name to connect. Also, sqllocaldb allows you to create new instances or delete them as well as configure them. See: SqlLocalDB Utility.

这将列出您在本地安装的实例名。因此，您的完整服务器名应该包含(localdb)\在实例名前面连接。另外，sqllocaldb允许您创建新实例或删除它们，以及配置它们。看:SqlLocalDB效用。

#7

SQL Server Browser Service http://msdn.microsoft.com/en-us/library/ms181087.aspx

SQL Server浏览器服务http://msdn.microsoft.com/en-us/library/ms181087.aspx。

#8

SQL Server permits applications to find SQL Server instances within the current network. The SqlDataSourceEnumerator class exposes this information to the application developer, providing a DataTable containing information about all the visible servers. This returned table contains a list of server instances available on the network that matches the list provided when a user attempts to create a new connection, and expands the drop-down list containing all the available servers on the Connection Properties dialog box. The results displayed are not always complete. In order to retrieve the table containing information about the available SQL Server instances, you must first retrieve an enumerator, using the shared/static Instance property:

SQL Server允许应用程序在当前网络中查找SQL服务器实例。SqlDataSourceEnumerator类将此信息公开给应用程序开发人员，提供了包含所有可见服务器的数据表。这个返回表包含一个网络上可用的服务器实例列表，该列表与用户尝试创建新连接时提供的列表相匹配，并且扩展了包含连接属性对话框中所有可用服务器的下拉列表。显示的结果并不总是完整的。为了检索包含有关可用SQL服务器实例的信息的表，您必须首先使用共享/静态实例属性检索枚举器:

using System.Data.Sql;

class Program
{
  static void Main()
  {
    // Retrieve the enumerator instance and then the data.
    SqlDataSourceEnumerator instance =
      SqlDataSourceEnumerator.Instance;
    System.Data.DataTable table = instance.GetDataSources();

    // Display the contents of the table.
    DisplayData(table);

    Console.WriteLine("Press any key to continue.");
    Console.ReadKey();
  }

  private static void DisplayData(System.Data.DataTable table)
  {
    foreach (System.Data.DataRow row in table.Rows)
    {
      foreach (System.Data.DataColumn col in table.Columns)
      {
        Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
      }
      Console.WriteLine("============================");
    }
  }
}

from msdn http://msdn.microsoft.com/en-us/library/a6t1z9x2(v=vs.80).aspx

从msdn http://msdn.microsoft.com/en-us/library/a6t1z9x2(v = vs.80). aspx

#9

If you are interested in determining this in a script, you can try the following:

如果您有兴趣在脚本中确定这一点，您可以尝试以下方法:

sc \\server_name query | grep MSSQL

Note: grep is part of gnuwin32 tools

注:grep是gnuwin32工具的一部分。

#10

From Windows command-line, type:

从Windows命令行中,键入:

SC \\server_name query | find /I "SQL Server ("

Where "server_name" is the name of any remote server on which you wish to display the SQL instances.

其中“server_name”是您希望显示SQL实例的任何远程服务器的名称。

This requires enough permissions of course.

当然，这需要足够的权限。

#11

I had the same problem. The "osql -L" command displayed only a list of servers but without instance names (only the instance of my local SQL Sever was displayed). With Wireshark, sqlbrowser.exe (which can by found in the shared folder of your SQL installation) I found a solution for my problem.

我遇到了同样的问题。“osql -L”命令只显示了一个服务器列表，但是没有实例名(只显示了本地SQL Sever的实例)。Wireshark数据浏览器。exe(可以在SQL安装的共享文件夹中找到)，我找到了解决问题的方法。

The local instance is resolved by registry entry. The remote instances are resolved by UDP broadcast (port 1434) and SMB. Use "sqlbrowser.exe -c" to list the requests.

本地实例由注册表项解析。远程实例由UDP广播(端口1434)和SMB解决。使用“数据浏览器。exe -c“列出请求”。

My configuration uses 1 physical and 3 virtual network adapters. If I used the "osql -L" command the sqlbrowser displayed a request from one of the virtual adaptors (which is in another network segment), instead of the physical one. osql selects the adpater by its metric. You can see the metric with command "route print". For my configuration the routing table showed a lower metric for teh virtual adapter then for the physical. So I changed the interface metric in the network properties by deselecting automatic metric in the advanced network settings. osql now uses the physical adapter.

我的配置使用了1个物理和3个虚拟网络适配器。如果我使用“osql -L”命令，sqlbrowser将从一个虚拟适配器(在另一个网络段)中显示请求，而不是物理适配器。osql通过它的度量选择adpater。您可以看到带有命令“路由打印”的度量。对于我的配置，路由表显示了用于物理的teh虚拟适配器的更低的度量。因此，我通过在高级网络设置中取消选择自动度量来改变网络属性中的接口度量。osql现在使用物理适配器。

#12

This query should get you the server name and instance name :

这个查询应该得到服务器名和实例名:

SELECT @@SERVERNAME, @@SERVICENAME

#13

I had this same issue when I was assessing 100+ servers, I had a script written in C# to browse the service names consist of SQL. When instances installed on the server, SQL Server adds a service for each instance with service name. It may vary for different versions like 2000 to 2008 but for sure there is a service with instance name.

当我评估100+服务器时，我遇到了同样的问题，我有一个用c#编写的脚本，用来浏览服务名称是由SQL组成的。当在服务器上安装实例时，sqlserver为每个实例添加服务名。不同的版本可能会有不同的版本，比如2000年到2008年，但肯定有一个具有实例名的服务。

I take the service name and obtain instance name from the service name. Here is the sample code used with WMI Query Result:

我从服务名获取服务名并获取实例名。下面是WMI查询结果中使用的示例代码:

if (ServiceData.DisplayName == "MSSQLSERVER" || ServiceData.DisplayName == "SQL Server (MSSQLSERVER)")
            {
                InstanceData.Name = "DEFAULT";
                InstanceData.ConnectionName = CurrentMachine.Name;
                CurrentMachine.ListOfInstances.Add(InstanceData);
            }
            else
                if (ServiceData.DisplayName.Contains("SQL Server (") == true)
                {
                    InstanceData.Name = ServiceData.DisplayName.Substring(
                                            ServiceData.DisplayName.IndexOf("(") + 1,
                                            ServiceData.DisplayName.IndexOf(")") - ServiceData.DisplayName.IndexOf("(") - 1
                                        );
                    InstanceData.ConnectionName = CurrentMachine.Name + "\\" + InstanceData.Name;
                    CurrentMachine.ListOfInstances.Add(InstanceData);
                }
                else
                    if (ServiceData.DisplayName.Contains("MSSQL$") == true)
                    {
                        InstanceData.Name = ServiceData.DisplayName.Substring(
                                                ServiceData.DisplayName.IndexOf("$") + 1,
                                                ServiceData.DisplayName.Length - ServiceData.DisplayName.IndexOf("$") - 1
                                            );

                        InstanceData.ConnectionName = CurrentMachine.Name + "\\" + InstanceData.Name;
                        CurrentMachine.ListOfInstances.Add(InstanceData);
                    }

#14

If your within SSMS you might find it easier to use:

如果你在ssm之内，你可能会发现它更容易使用:

SELECT @@Version

#15

I just installed Sql server 2008, but i was unable to connect to any database instances. The commands @G Mastros posted listed no active instances.

我刚刚安装了Sql server 2008，但是我无法连接到任何数据库实例。@G Mastros的命令没有列出任何活动实例。

So i looked in services and found that the SQL server agent was disabled. I fixed it by setting it to automatic and then starting it.

所以我查看了服务，发现SQL server代理被禁用了。我把它设置为自动，然后启动它。

#16

Here is a simple method: go to Start then Programs then Microsoft SQL Server 2005 then Configuration Tools then SQL Server Configuration Manager then SQL Server 2005 Network Configuration then Here you can locate all the instance installed onto your machine.

这里有一个简单的方法:先启动程序，然后是Microsoft SQL Server 2005，然后配置工具，然后是SQL Server Configuration Manager，然后是SQL Server 2005网络配置，然后在这里你可以找到安装在你机器上的所有实例。

#17

I know its an old post but I found a nice solution with PoweShell where you can find SQL instances installed on local or a remote machine including the version and also be extend get other properties.

我知道它是一个旧的帖子，但是我找到了一个很好的解决方案，在这里你可以找到安装在本地或远程机器上的SQL实例，包括版本，还可以扩展其他属性。

$MachineName = ‘.’ # Default local computer Replace . with server name for a remote computer

$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey(‘LocalMachine’, $MachineName)
$regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL" )
$values = $regkey.GetValueNames()
$values | ForEach-Object {$value = $_ ; $inst = $regKey.GetValue($value); 
              $path = "SOFTWARE\\Microsoft\\Microsoft SQL Server\\"+$inst+"\\MSSQLServer\\"+"CurrentVersion";
              #write-host $path; 
              $version = $reg.OpenSubKey($path).GetValue("CurrentVersion");
                          write-host "Instance" $value;
              write-host  "Version" $version}

#18

One more option would be to run SQLSERVER discovery report..go to installation media of sqlserver and double click setup.exe

另一个选项是运行SQLSERVER发现报告。进入sqlserver的安装媒体，双击setup.exe。

and in the next screen,go to tools and click discovery report as shown below

在下一个屏幕中，切换到工具并单击“发现报告”，如下所示。

This will show you all the instances present along with entire features..below is a snapshot on my pc

这将显示所有的实例以及整个特性。下面是我个人电脑的截图。