Wednesday, October 19, 2011

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

No comments:

Post a Comment