Query machine name of the sql server instance - the hard or the harder way

I was at a client where they are using non-Microsoft clustering technology to achieve the high availability of SQL Server instances. This was partly because of legacy reasons and partly because it supports clustering across all major hardware, operating systems, and applications, including SQL Server. SQL Server instances are set up in either 2 or 3 nodes, active/passive, active/active, etc., configurations. There are approximately 30 physical servers hosting SQL Server instances. Yes, the client is going to move all the SQL workloads to Always On Clusters, but the process has been slow because all the databases are used for COTS/third-party applications.

A virtual name is used to make a connection to a SQL Server instance. Sometimes, I would need to know the actual physical node name where a particular SQL instance is active, and I needed to find it out programmatically.

You may have different reasons for connecting to SQL Server using a virtual name but need to know the underlying machine name.

So I first tried this query:

SELECT   

  @@SERVERNAME ServerName_Global_Variable
 ,SERVERPROPERTY('ServerName') ServerName
 ,SERVERPROPERTY('InstanceName') InstanceName
 ,SERVERPROPERTY('MachineName') MachineName
 ,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') NetBIOS

That would be the easier way if it worked in this situation. Alas, it still kept giving me virtual server name.

Then I tried using a DOS command, assuming you have or are able to temporarily turn on XP_CMDSHELL.

EXEC master..XP_CMDSHELL 'Hostname'

And I still got the virtual server name.

Then I tried the following which does display the actual server name in one of the messages:

EXEC master..xp_cmdshell 'net user'

Mission accomplished, great! I could write additional code around it to trim out every other text from it, extracting only the computer name, and then store it in a variable or in my own metadata table for further processing, reporting, etc.

Then I thought, is there another way, perhaps a better way? Well, I wouldn't necessarily call my next approach better, but it's certainly another way.

If you're looking for a one-liner to remotely look up a computer name, simply run the following command from the command prompt or PowerShell:

wmic /NODE:sqlservernode1 computersystem get Name

Here is a bit lengthy TSQL code to do this while connected to a SQL instance. Note that if the XP_CMDSHELL is disabled,  it temporarily turns it on then off when done.

/**********************************************************************
-- Script Purpose:
--   Retrieve the current computer/server name using WMIC via xp_cmdshell.
--
-- Description:
--   This script:
--     1. Checks if xp_cmdshell is enabled.
--     2. Enables it temporarily if needed.
--     3. Executes "wmic computersystem get Name" to get the hostname.
--     4. Captures and parses the output from xp_cmdshell.
--     5. Returns the server name as a single value.
--     6. Reverts xp_cmdshell to its prior state if it was originally disabled.
--
-- Requirements:
--   - Requires sysadmin privileges.
--   - xp_cmdshell must be available for use (can be toggled dynamically).
--
-- Notes:
--   - Uses a temporary table (#t1_xp_cmdshell_output) to capture WMIC output.
--   - Assumes SQL Server runs under an account with permission to execute WMIC.
--
-- Author: 
-- Last Updated:   2026-02-24
**********************************************************************/

SET NOCOUNT ON;

DECLARE @computer_name VARCHAR(500);
DECLARE @xp_cmdshell_status BIT;

-- Check the current xp_cmdshell configuration state
SELECT @xp_cmdshell_status = CAST(value_in_use AS BIT)
FROM sys.configurations
WHERE name = 'xp_cmdshell';

-- Enable xp_cmdshell temporarily if it's currently disabled
IF @xp_cmdshell_status = 0
BEGIN
    PRINT 'xp_cmdshell is disabled on this server. Temporarily enabling it...';

    EXEC sp_configure 'show advanced options', 1;
    RECONFIGURE;

    EXEC sp_configure 'xp_cmdshell', 1;
    RECONFIGURE;
END;

IF OBJECT_ID('tempdb..#t1_xp_cmdshell_output') IS NOT NULL
    DROP TABLE #t1_xp_cmdshell_output;

-- Create a table to capture xp_cmdshell output
CREATE TABLE #t1_xp_cmdshell_output
(
    id INT IDENTITY(1,1),
    txt NVARCHAR(2000)
);

-- Execute WMIC command to fetch the computer name
-- xp_cmdshell output returns multiple rows (header + blank line + data)
INSERT INTO #t1_xp_cmdshell_output
EXEC master..xp_cmdshell 'wmic computersystem get Name';

-- The actual computer name is on line 2 of the output
SELECT @computer_name = txt
FROM #t1_xp_cmdshell_output
WHERE id = 2;

-- Return the detected computer name
SELECT @computer_name AS computer_name;

-- Restore xp_cmdshell to its original configuration if it was disabled earlier
IF @xp_cmdshell_status = 0
BEGIN
    PRINT 'Returning xp_cmdshell to its disabled state...';

    EXEC sp_configure 'show advanced options', 1;
    RECONFIGURE;

    EXEC sp_configure 'xp_cmdshell', 0;
    EXEC sp_configure 'show advanced options', 0;
    RECONFIGURE;
END;

Generally, when I'm using a DOS or PowerShell command, I prefer using PowerShell to populate the data in the SQL table, avoiding all the additional code I've used in the above T-SQL block.

And finally, there is one more way, sort of! If you execute a multi-server query with the "Add server name to the results" option set to true (default), it will display the physical server name in the results pane. However, I'm not aware of a way to capture it dynamically in a variable at this point.

If you think I missed something, please don't hesitate to provide feedback!