CODEWITHSUNDEEP
sql-serversql-server-express
Luke
Luke

Reputation: 18983

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

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.

Upvotes: 265

Views: 745605

Answers (22)

MAF
MAF

Reputation: 99

For Instance Name and version you can run the following in a new query window:

SELECT @@SERVICENAME

and for version

SELECT @@VERSION

Upvotes: 0

vernou
vernou

Reputation: 7610

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

enter image description here

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

enter image description here

Upvotes: 2

siva
siva

Reputation: 67

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

or Use SQLCMD -L

Upvotes: 1

Erk
Erk

Reputation: 1209

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:

  1. Start Windows Task Manager
  2. Tick the checkbox "Show processes from all users" or equivalent
  3. Sort the processes by "Image Name"
  4. 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...)

Upvotes: 2

TheGameiswar
TheGameiswar

Reputation: 28940

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

enter image description here

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

enter image description here

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

Upvotes: 8

jimbo
jimbo

Reputation: 51

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.

Upvotes: 5

akhil vangala
akhil vangala

Reputation: 1063

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}

Upvotes: 0

John Denton
John Denton

Reputation: 371

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.

Upvotes: 27

Craig
Craig

Reputation: 1

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

SELECT @@Version

Upvotes: -5

George Mastros
George Mastros

Reputation: 24498

At a command line:

SQLCMD -L

or 

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

Upvotes: 227

AbuTaareq
AbuTaareq

Reputation: 111

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

Upvotes: 1

Dale Sykora
Dale Sykora

Reputation: 51

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

Upvotes: 5

Anonymous
Anonymous

Reputation: 85

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

SELECT @@SERVERNAME, @@SERVICENAME

Upvotes: 6

Badar
Badar

Reputation: 1460

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.

Upvotes: 0

Ian
Ian

Reputation: 170

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

Upvotes: 8

Mohammed Ifteqar Ahmed
Mohammed Ifteqar Ahmed

Reputation: 551

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

Upvotes: 55

Moulde
Moulde

Reputation: 3506

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.

Upvotes: 1

Daniel
Daniel

Reputation: 21

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.

Upvotes: 2

Matt
Matt

Reputation: 5172

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

Upvotes: 11

Brian R. Bondy
Brian R. Bondy

Reputation: 347556

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.

Upvotes: 83

dotnetengineer
dotnetengineer

Reputation: 1302

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

Upvotes: 86

Cade Roux
Cade Roux

Reputation: 89741

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

Upvotes: 5

Related Questions