SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to find out Installed SQL Server Instances on a server


How to find out Installed SQL Server Instances on a server

Author
Message
Raghavender Chavva
Raghavender Chavva
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1576 Visits: 1287
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
Raghavender Chavva
Raghavender Chavva
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1576 Visits: 1287
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
Raghavender Chavva
Raghavender Chavva
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1576 Visits: 1287
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
sqlbuddy123
sqlbuddy123
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3068 Visits: 2243
Check if this helps..

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

Thank You,

Best Regards,
SQLBuddy
crazy4sql
crazy4sql
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2165 Visits: 4510
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
sqlbuddy123
sqlbuddy123
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3068 Visits: 2243
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
crazy4sql
crazy4sql
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2165 Visits: 4510

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

Thank You,

Best Regards,
SQLBuddy


100 instance in one server. w00t w00t tell me your server name and I am going to report it to MS w00tHehe:-D

----------
Ashish
sqlbuddy123
sqlbuddy123
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3068 Visits: 2243
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. w00t w00t tell me your server name and I am going to report it to MS w00tHehe:-D


No w00t .. Please don't report it to MS :-D ..

Thank You,

Best Regards,
SQLBuddy
rama38udaya
rama38udaya
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 497
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
Wayne Evans-440401
Wayne Evans-440401
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 274
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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search