Friday, May 11, 2018

How to query all of the named instances for a SQL Server?

Easy sneasy.  I'm performing a health check for a new customer over the weekend, and when I logged into the server today, I was greeted by a named instance that I wasn't aware of.  Heck.  I didn't know there any named instances!  So now I'm asking myself how many there are, and which ones I should be auditing.

This is just a quick piece just to list the named instances for any given SQL Server.  Super easy.  Check it out and let me know what you think.

-- how many instances are there?
DECLARE @Instances TABLE (
       Value VARCHAR(100),
       InstanceName VARCHAR(100),
       Data VARCHAR(100)

INSERT @Instances
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances' 

-- return your data
SELECT InstanceName FROM @Instances

These are my results:


No comments:

Post a Comment