Query to find Machine name, node names, SQL Server instances names and Active Node Name

Finally!!!! Finally!!!!!

After long wait I got time to find out the actual machine name and different cluster nodes of it and different SQL instances installed on it and the current active node among the nodes and along with SQL Server Version. WOW it cleared out my many confusions.

SELECT SERVERPROPERTY('MachineName') AS MachineName

go

SELECT * FROM sys.dm_os_cluster_nodes

go

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

Insert into @Instances

EXECUTE xp_regread  @rootkey = 'HKEY_LOCAL_MACHINE',

@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',  @value_name = 'InstalledInstances'

Select InstanceNames from @Instances

go

Select SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as ActiveNode

go

SELECT @@VERSION

Links …