Get a List of all SQL Instances on a Server

By Lori Brown | Helpful Scripts

Jun 08

It is pretty common that I frequently am asked to take over a server with SQL on it sight unseen. Many times shops many not be fully aware if additional instances are or have been installed on their server. Since I am working on a set of scripts that we can use for discovery on new systems, I thought it would be handy to have a query that would give me a list of SQL instances with the version that are installed.

I found that this kind of information can be found in the registry which is accessible via a couple of undocumented extended procedures. Let me give the disclaimer right here…. Undocumented procedures from Microsoft are not supported and should not be used for regular production work. This is because they can change or be removed at any time. Now that I have stated that, the other obvious statement is that undocumented procedures can be pretty handy for some things, just use them with caution.

My query uses xp_regread and xp_instance_regnumvalues. Both will read values from the registry in the key values you point them at.

And, here are the results from one of our dev servers.

This only gives a list of the instances found in the registry. It does not tell you if the instances are actually running or not so there is still some investigation to be done. Of course I know you can always just open the SQL Configuration Manager to find things but then I would not have had the fun of making a cool query would I?!

If you ever need to find out exactly what build and service pack or CU you might have installed, you can always use https://sqlserverbuilds.blogspot.com/ for a comprehensive list.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!

  • […] Lori Brown has a script which lists installed SQL Server instances: […]

  • >