Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to find out Installed SQL Server Instances on a server Expand / Collapse
Author
Message
Posted Tuesday, September 13, 2011 11:53 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:23 AM
Points: 732, Visits: 853
Hi All,

How to find out Installed SQL Server Instances and their names on a server using T-SQL or any other way ?


Thanks and Regards!!

Raghavender Chavva
Post #1174649
Posted Wednesday, September 14, 2011 2:52 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:23 AM
Points: 732, Visits: 853
I think below will give the expected result:

-- Create Temporary table to store the data
Create Table #SQLInstances
( Value nvarchar(100),
InstanceName nvarchar(100),
Data nvarchar(100))

-- Read Data from Registery
Insert into #SQLInstances
EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name = 'InstalledInstances'

Select InstanceName from #SQLInstances


Thanks and Regards!!

Raghavender Chavva
Post #1174717
Posted Wednesday, September 14, 2011 2:52 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:23 AM
Points: 732, Visits: 853
Raghavender (9/14/2011)
I think below will give the expected result:

-- Create Temporary table to store the data
Create Table #SQLInstances
( Value nvarchar(100),
InstanceName nvarchar(100),
Data nvarchar(100))

-- Read Data from Registery
Insert into #SQLInstances
EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name = 'InstalledInstances'

Select InstanceName from #SQLInstances


Here can we find out which instance is running and which is not ?


Thanks and Regards!!

Raghavender Chavva
Post #1174718
Posted Wednesday, September 14, 2011 7:06 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:57 PM
Points: 1,194, Visits: 2,211
Check if this helps..

http://www.sqlservercentral.com/scripts/Administration/71614/

Thank You,

Best Regards,
SQLBuddy
Post #1175385
Posted Wednesday, September 14, 2011 10:24 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:28 AM
Points: 880, Visits: 4,084
Raghavender (9/13/2011)
Hi All,

How to find out Installed SQL Server Instances and their names on a server using T-SQL or any other way ?


The easiest way for me is, go to services and check the sql server services.
If its named instance then your sql service will be named as mssql$instancename, if its default instance then simply mssqlserver.


----------
Ashish
Post #1175410
Posted Wednesday, September 14, 2011 10:39 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:57 PM
Points: 1,194, Visits: 2,211
crazy4sql (9/14/2011)
Raghavender (9/13/2011)
Hi All,

How to find out Installed SQL Server Instances and their names on a server using T-SQL or any other way ?


The easiest way for me is, go to services and check the sql server services.
If its named instance then your sql service will be named as mssql$instancename, if its default instance then simply mssqlserver.


I think this becomes bit difficult if we have 100 instances or more ..

Thank You,

Best Regards,
SQLBuddy
Post #1175414
Posted Wednesday, September 14, 2011 10:44 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:28 AM
Points: 880, Visits: 4,084

I think this becomes bit difficult if we have 100 instances or more ..

Thank You,

Best Regards,
SQLBuddy


100 instance in one server. tell me your server name and I am going to report it to MS


----------
Ashish
Post #1175415
Posted Wednesday, September 14, 2011 10:52 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:57 PM
Points: 1,194, Visits: 2,211
crazy4sql (9/14/2011)

I think this becomes bit difficult if we have 100 instances or more ..

Thank You,

Best Regards,
SQLBuddy


100 instance in one server. tell me your server name and I am going to report it to MS


No .. Please don't report it to MS ..

Thank You,

Best Regards,
SQLBuddy
Post #1175417
Posted Sunday, September 18, 2011 3:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:29 AM
Points: 50, Visits: 448
Hi,

Get SQL server information in OS registry levels.


Irrespective of the version of SQL Server, information is saved in the registry. Only the location changes for various versions. The path in the registry is as follows.
SQL Server Default Instance(2000)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
SQL Server Default Instance(2005)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup
SQL Server Named Instance (2008)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.ONE\Setup

Best Regards,
Rama Udaya.K
Post #1176960
Posted Monday, September 19, 2011 1:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 6:22 AM
Points: 114, Visits: 244
If you can lookup how to capture info from a command prompt and put it into a table, you could use the command:

Net start

To list all running processes. Joined with the the info from the registry, you cab figure out the started and stopped versions of SQL.

If you want to keep it all in the registry, they key will be something like

Hklm\services\control\current version\mssql(instance)

(^^^ the above key is not correct as I'm not near a windows PC to check. It is approximately right.)

This will give you the actual status of each service, e.g. start, stopped, paused (probably numeric values for each). Overkill?

If it were me, I'd use the net start command as I don't need to list all the registry keys and then search its values.




Wayne

Did you get access denied? Great the security works.

Post #1177095
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse