Wednesday, July 29, 2015

Query to list all SQL Server instances installed on a server

I am preparing an upgrade for one of my customers. It's a rolling upgrade of a v2008 mirror session, to v2012. This particular customer has a tendency of installing new instances without telling me, so I wanted to quickly check all instances on the server that I would be upgrading.

I know it's in the registry, at this location:
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names

But I'm always trying to query details like this from the engine... I am sure there are things that I cannot query from the system catalogs, but this isn't one of them. :-)  Check it out, this is MUCH quicker than regedit!

        DECLARE @instances TABLE ( 
             value CHAR(100),
              instances CHAR(100),
             data CHAR(100)

INSERT @instances (value,instances,data)
EXECUTE xp_regread
            @rootkey = 'HKEY_LOCAL_MACHINE',
            @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
            @value_name = 'InstalledInstances'

/* look at your data */
instances [InstanceNames]

These are the instance names from my own server:

Pretty cool.  

No comments:

Post a Comment