SQL Server : List all failover clustered instance nodes on which an instance of SQL Server can run and Find active node query

SELECT * FROM ::fn_virtualservernodes()

Above query returns the list of Nodes on which SQL Server can run. If the current server instance is not a clustered server, fun_virtualservernodes returns empty rowset.

User must have VIEW SERVER STATE permission to execute above query successfully. 

Note : if you have SQL 2000 , Microsoft recommending to use

Select * from sys.dm_os_cluster_nodes

How to determine the active node of a SQL Active-Passive Failover Cluster programmatically from T-SQL?

Select ServerProperty('ComputerNamePhysicalNetBIOS')