Agent Jobs

Get List Of SQL Instances Installed

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_instances
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s