Hi,

When i have multiple sql server instances running on one server, how to check the names of that instances with out connecting to sql server management studio.

Thanks

Shekar

1)cmd => sqlcmd -L or osql -L will list sql server

2)query :- select @@servername + '\' + @@servicename

Hi,

When i have multiple sql server instances running on one server, how to check the names of that instances with out connecting to sql server management studio.

Thanks

Shekar

 

Or you can run Discovery report if you have SQL Server 2008 and above

or go to cmd and type osql -L

if you open the SQL server configuration manager and look for "SQL services" you will see all the instances.

Default instance is MSSQLSERVER. only one default instance is allowed per server.

Any other instance should be a named instance. 

At a command line (press WINDOWS+R or hit START and then type CMD):

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

Also, you can 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')

• Edited by Andrey Langovoy 1 hour 17 minutes ago

At a command line (press WINDOWS+R or hit START and then type CMD):

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

Also, you can 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')

• Edited by Andrey Langovoy Friday, July 03, 2015 5:48 AM
• Proposed as answer by Andrey Langovoy 19 hours 39 minutes ago

At a command line (press WINDOWS+R or hit START and then type CMD):

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

Also, you can 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')

• Edited by Andrey Langovoy Friday, July 03, 2015 5:48 AM
• Proposed as answer by Andrey Langovoy Friday, July 03, 2015 11:28 AM

Note that the discovery mechanisms are not 100% guaranteed. The guaranteed way to see what instances are installed is to check the services (using either "services" or "SQL Server Configuration Manager" (or something similar tool/Script that does in the end check the registry for installed instance

Shanky, I didn't know it might return incorrect version... Good to know. I never use browsing because it being unreliable.

Not returning all instances on the network is pretty obvious, and I believe that this wasn't what you were referring to.

But I've also seen plenty of reports where this switch didn't list instances on the local machine either. I never bothered to investigate this, but rather shrugged and considered it being among "might work, might not" things. :-)