How to tell if SQL Server is local or remote?

Question

In my app the users gets to pick from a list of SQL Server in the network. The thing is I need to know if the chosen instance is a local or remote computer.

Is there a way I can ask that SQL instance what computer is she on? Is there a way a can figure that out?

Edit1: I want to know the host name where the SQL Server is hosted so I can then compare that to System.Environment.MachineName and know for sure is that Sql Server is running on the same machine where my app is running

Edit2: @@servername returned my computername\sqlinstance while SELECT SERVERPROPERTY('MachineName') returns just the computername, which is exactly what I want

Answer 1

Use @@Servername, for example:

SELECT @@servername

Alternately you could do this

SELECT SERVERPROPERTY('MachineName') 

From MSDN on the differences between these approaches:

The ServerName property of the SERVERPROPERTY function and @@SERVERNAME return similar information. The ServerName property provides the Windows server and instance name that together make up the unique server instance. @@SERVERNAME provides the currently configured local server name.

The ServerName property and @@SERVERNAME return the same information if the default server name at the time of installation has not been changed.

If the local server name has been changed from the default server name at installation time, @@SERVERNAME returns the new name.

Answer 2

sp_who2 returns the hostname

Answer 3

Do you actually have login permissions on all the instance(s) of SQL Server? If so you could execute sp_helpserver or @@servername and compare the name returned with Environment.MachineName.

If you don't have login access, you can write a small C# console program to return the server name of every SQL Server instance on the local network:

using System;
using System.Data.Sql;

class Program
{
    static void Main()
    {
        // Retrieve the enumerator instance and then the data.
        SqlDataSourceEnumerator instance =
        SqlDataSourceEnumerator.Instance;
        System.Data.DataTable table = instance.GetDataSources();

        // Display the contents of the table.
        // The first column is the server name.
        DisplayData(table);
        Console.WriteLine("Press any key to continue.");
        Console.ReadKey();
    }

    private static void DisplayData(System.Data.DataTable table)
    {
        foreach ( System.Data.DataRow row in table.Rows )
        {
            foreach ( System.Data.DataColumn col in table.Columns )
            {
                    Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
            }
        Console.WriteLine("============================");
        }
    }
}