How can I determine installed SQL Server instances and their versions?

Asked 26/9, 2008 at 18:32 Answered 18/7, 2023 at 15:4

261

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.

Gunmaker answered 26/9, 2008 at 18:32 Comment(6)

Here is a link how to identify with sqlcmd msdn.microsoft.com/en-us/library/ms165662%28v=sql.90%29.aspx – Ratiocination 12/6, 2013 at 8:19

How to Find Your SQL Server Instances (Server Name) and Versions youtube.com/watch?v=DLrxFXXeLFk – Potentilla 4/11, 2014 at 14:23

can you tell me the SQL queries you are speaking of? – Granadilla 12/2, 2016 at 14:49

@Granadilla See Mohammed Ifteqar Ahmed's answer below. – Gunmaker 22/2, 2016 at 13:10

You can interrogate the registry: pmichaels.net/2016/02/12/… – Fortna 28/2, 2016 at 15:47

The other solutions did not worked for me Cause I am sure I have few named instances , these solutions just shows default instance. But Visual Studios SQL Server Object Explorer Window lists all local named and unnamed instances of sql server. Just hit add sql server image button and under local arrow all instances are listed. Maybe this helps – Equi 29/12, 2016 at 22:59

228

At a command line:

SQLCMD -L

OSQL -L

(Note: must be a capital 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...

At command line:

svrnetcn

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

Schnitzler answered 26/9, 2008 at 18:45 Comment(8)

I like the command-line options, but I got mixed results when I tried them on my (non-networked) developer box; basically "sqlcmd -L" was the only one that worked, and only if the SQL Server Browser Service was running. Is that to be expected? – Elishaelision 27/9, 2008 at 9:32

I LOVE it when it is SIMPLE and STRAIGHT. I was struggling to find the right server name to enter for an Amazon Web Service SQL Server instance. ThanX – Yorick 6/5, 2011 at 11:40

Nice commands but for some reason it detected the SQLExpress instance on a network computed but failed to detect the SQLExpress instance on my local machine. – Mcclish 5/5, 2015 at 18:41

@sparebytes: reason here: dba.stackexchange.com/questions/18499/… – Emeliaemelin 22/1, 2016 at 15:5

Try: C:\> sqllocaldb i – Employer 19/12, 2016 at 20:46

What is the cmd code for Mac? I tried the same code in Mac terminal but not working... – Triptych 24/1, 2020 at 17:18

I don't know anything about Mac. Would this help: cloudblogs.microsoft.com/sqlserver/2017/04/03/… – Schnitzler 26/1, 2020 at 18:58

I'm trying to do this from a fresh docker container but am getting an SSPI context error. SQLCMD works fine in other ways but this doesn't. – Eighteenmo 9/2, 2022 at 17:42

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)".

Love answered 26/9, 2008 at 18:37 Comment(2)

Equivalent PowerShell command: Get-Service | ?{ $_.Name -like "MSSQL*" } – Rufina 11/2, 2014 at 4:38

My computer shows MSSQL$SQLEXPRESS is running with display name SQL Server (SQLEXPRESS).... but how do i enter this in a server name? Test connection shows errors like ... a network related or instance specific error occured when trying to connect to sql server – Salenasalene 6/2, 2016 at 9:29

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

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:

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.

Radioactivate answered 26/9, 2008 at 18:34 Comment(4)

('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') shows only server, only running one but not instances or installed but stopped servers – Clang 13/10, 2010 at 2:45

Thanks, the query worked for me. I had a machine and wanted to know default instance and SQL Express instance which was 2008 and which 2008 R2. I connected to each instance and ran the query and it got me a version number. Googling the numbers then was easy. – Alda 27/12, 2010 at 3:15

Note, it seems like the "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<version>\Tools\ClientSetup\CurrentVersion" and "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names" shows up in the 32bit portion of the registry, while the actual path to the instance: "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instancename>" shows up in the 64bit Hive. – Humbuggery 11/10, 2013 at 16:45

The registry is the source definition of installed software. Your solution allows me to go directly to the source, rather than using a CLI tool, which ultimately uses registry values, or MMC snap-in which also uses the registry. Perfect – Simplicity 18/10, 2017 at 15:55

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

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

Mycosis answered 27/2, 2012 at 12:6 Comment(4)

Good one! I just think it's required to connect as sa isn't it? – Domeniga 23/5, 2012 at 13:59

+1 The source of the information about the instances is the same as the answer by Brian. Someone might as well write C# code to get the value from the Windows Registry; which made me think the answer is redundant for moment but it's nice to know about xp_regread. #thanks. – Efrenefron 29/10, 2014 at 4:56

The query results are Instance Names only, Is it possible to add compatibility level for each instance to results? – Mentor 22/10, 2017 at 11:26

This won't differentiate between instances associated with a full version and an express version of SQL Server – Paulus 13/8, 2021 at 15:17

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:

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

Lengthy answered 14/11, 2016 at 21:42 Comment(0)

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).

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

Elishaelision answered 27/9, 2008 at 9:18 Comment(1)

SQL Server Configuration Manager was exactly what I needed. Quick and easy. – Parthenope 2/1, 2011 at 2:57

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:

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

Rabb answered 21/3, 2012 at 21:19 Comment(2)

do I need to specify a subnet on this or domain, its coming back null for me, can you please update – Paintbox 7/1, 2017 at 19:58

For those looking to use .NET Core, SqlDataSourceEnumerator is not yet implemented, but is on the list to be added according to the GitHub issue. – Gusti 24/6, 2019 at 15:41

One more option would be to run SQLSERVER discovery report..go to installation media of sqlserver and double click 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

Spermatic answered 16/1, 2018 at 14:58 Comment(1)

This is the best way to get all the instances. None of the above high voted solutions can give a complete list as this method. It even shows MSSQL Express LocalDB versions installed in the computer even though it is not related to the original question about "Instances". – Curson 11/3, 2021 at 23:30

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

SELECT @@SERVERNAME, @@SERVICENAME

Hugo answered 22/8, 2012 at 1:11 Comment(2)

This only tells you the name of the current instance associated with the executing query. OP requested a list of all the installed instances – Canal 19/3, 2014 at 17:0

This does not provide any information about which version of SQL server is installed – Io 1/3, 2015 at 8:29

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

Fushih answered 26/9, 2008 at 18:35 Comment(1)

oops... missing sqlbrowser.exe from usual location! I must have the Microsoft edition (ie the one that doesn't work) :-) Kidding - we all love Microsoft, almost as much as my mother-in-law. – Dr 25/2, 2011 at 5:47

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

Participation answered 21/6, 2011 at 19:41 Comment(2)

You can use findstr instead of grep for this. – Virgenvirgie 18/11, 2014 at 17:47

You can also use, FIND, which leads to sc \\server_name query | FIND "MSSQL" – Violaceous 21/11, 2019 at 23:35

From Windows command-line, type:

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.

This requires enough permissions of course.

Photovoltaic answered 7/10, 2013 at 16:26 Comment(1)

Not working on my dev machine, which has 2008 R2 and multiple Express and LocalDB instances running. – Lawrence 13/9, 2016 at 18:46

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.

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.

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.

Chrysa answered 22/12, 2010 at 10:30 Comment(0)

The commands OSQL -L and SQLCMD -L will show you all instances on the network.

If you want to have a list of all instances on the server and doesn't feel like doing scripting or programming, do this:

Start Windows Task Manager
Tick the checkbox "Show processes from all users" or equivalent
Sort the processes by "Image Name"
Locate all sqlsrvr.exe images

The instances should be listed in the "User Name" column as MSSQL$INSTANCE_NAME.

And I went from thinking the poor server was running 63 instances to realizing it was running three (out of which one was behaving like a total bully with the CPU load...)

Hither answered 17/10, 2019 at 14:46 Comment(0)

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.

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.

Shwa answered 14/12, 2011 at 0:34 Comment(0)

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.

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

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);
                    }

Schumann answered 28/3, 2013 at 17:39 Comment(0)

Will get the instances of SQL server reg query "HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL"

or Use SQLCMD -L

Michelinemichell answered 17/2, 2020 at 4:3 Comment(1)

Interestingly, only the Registry query approach worked for me. – Doriadorian 3/9, 2021 at 9:44

A solution is to open SQL Server Management Studio on the local machine. When you connect to a server, you can choose "<Browse for more...>" :

The window "Browse for Servers" opens. In the tab "Local Servers", you can unfold "Database Engine" :

Kruller answered 18/7, 2023 at 15:4 Comment(0)

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.

Sandhi answered 24/7, 2012 at 13:54 Comment(0)

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.

$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}

Matherne answered 1/12, 2016 at 15:42 Comment(2)

hi this is great how, can I wrap this inside a C# class or how to call this from code – Paintbox 7/1, 2017 at 19:59

Hi. This is my 1st attempt at powershell, so your help would be appreciated. I get the following error if I try and run this script. Aldo will this find all the remote sql servers? I am trying to compile a list of SQL Servers and their databases. You cannot call a method on a null-valued expression. + $values = $regkey.GetValueNames() – Erythromycin 27/3, 2019 at 12:49

-5

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

SELECT @@Version

Shelleyshellfire answered 7/9, 2015 at 12:28 Comment(1)

The question itself states they do not want to/are not able to use SQL queries to determine the version – Beck 7/9, 2015 at 12:40

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags