Here’s some quick SQL logic to show you all the instances that are installed on a server. This is simple enough, but still super helpful when creating more automatic processes around a multi-instance SQL Server.
use master; set nocount on declare @sql_instances table ( [id] int identity(1,1) , [rootkey] nvarchar(255) , [sql_instances] nvarchar(255) , [value] nvarchar(255) ) insert into @sql_instances ([rootkey], [sql_instances], [value]) execute xp_regread @rootkey = 'hkey_local_machine' , @key = 'softwaremicrosoftmicrosoft sql server' , @value_name = 'installedinstances' select 'SQL_Instances_Installed' = upper([sql_instances]) from @sql_instances
Also… If you needed to check to see if there were numeric values in the SQL Instnance name; You could use this:
select right([sql_instances], 2) , case when right([sql_instances], 2) not like '%[0-9]%' then 'This is NOT numeric' else right([sql_instances], 2) end as [sql_instances] from @sql_instances
On occasion you may run into this error based on key values (missing or otherwise):
RegOpenKeyEx() returned error 2, ‘The system cannot find the file specified.’
Msg 22001, Level 1, State 1
Since this error occurs it will not populate the temp table, and therefore will not return any instance names, but no worries as the xp_regread extended stored procedure is not the only approach you can make when reading the instance names from the registry.
You can also try this little number called xp_instance_regenumvalues. With a slight modification you can produce the same results and get back some valuable information including the Instant path as this is evaluated from the ‘key’ within enumvalues.declare @sql_instances table ( [rootkey] varchar(255) , [value] varchar(255) ) insert into @sql_instances exec master.dbo.xp_instance_regenumvalues @rootkey = N'HKEY_LOCAL_MACHINE' , @key = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL'; select 'Instance_Name' = upper([rootkey]) , 'Instance_Path' = upper([value]) from @sql_instancesAdvertisements
Categories: Agent Jobs, Alerts, AlwaysOn, Automations, Backup & Restore, Big Data, Cluster, Command Prompt, Configuration, Database Mail, Database Mirroring, ELI5, Error Solutions, Failover, Failover Cluster, Featured, Folder System, Forensics & Auditing, High Availability, How To, Installations, Long Scripts, Management Studio, Mirroring, Notification, Powershell, Quick Fix, Scripts, Security, Short Scripts, SQL Stuff, Top, TSQL, Work Arounds