Home > Query, SQL > BISQL # 73 – How To find list of SQL Server Instances Installed on any machine By Query

BISQL # 73 – How To find list of SQL Server Instances Installed on any machine By Query

Hi folks

Today suddenly in  I need to find out which SQL Server instance is installed on my machine , while researching this I found out query and one undocumented sp

We can use the xp_regread undocumented extended stored procedure to read from the registry via Transact-SQL.

Syntax for xp_regread

EXECUTE xp_regread [@rootkey=]'rootkey',
                   [@key=]'key'
                   [, [@value_name=]'value_name']
                   [, [@value=]@value OUTPUT] 

Following query I have used to find instance

you can use this query as it is n paste in your query plan n see result :

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 

Following is output for above query

image

After some more digging I just found following link which have solve all my problems but result was quite surprising

MSDN Blog:How to list all the installed SQL Server on the Server using TSQL

Result after above links

 image

And if you want this Details via .net go to Following link

MSDN Blog :Getting SQL Server registry settings via SQLCLR table-valued user-defined function 

Hope this Helps !!!!

Thanks for visiting my blog !!

Hope you will like this script on How To find list of SQL Server Instances Installed on a machine By Query

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where todays links are

Link Resource Website

About these ads
Categories: Query, SQL
  1. praveen.basappa@gmail.com
    January 17, 2012 at 11:05 pm | #1

    I am getting on to each topics : really good and interesting – thanks for sharing …

    • January 29, 2012 at 10:28 pm | #2

      Hey thanx a Lot Praveen !I am glad u liked it n reall thanx for reply !

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,115 other followers

%d bloggers like this: