T-SQL Query to find list of Instances Installed on a machine | SansSQL

Sunday, March 13, 2011

T-SQL Query to find list of Instances Installed on a machine

Here is a T-SQL Query to find the list of instances Installed on a machine.

DECLARE @GetInstances TABLE
( Value nvarchar(100),
 InstanceNames nvarchar(100),
 Data nvarchar(100))

Insert into @GetInstances
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances'
 
Select InstanceNames from @GetInstances

OR

Create Table #GetInstances
( Value nvarchar(100),
 InstanceNames nvarchar(100),
 Data nvarchar(100))

Insert into #GetInstances
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances'
 
Select InstanceNames from #GetInstances

drop table #GetInstances

Both the queries are almost similar, except for that first query uses a table variable and the second one uses temporary table.

No comments:

Post a Comment