SQL Server Mentalist

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

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

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