Check if a SQL Server instance is on local computer?

Question

I would like to identify if the SQL Server I'm connecting to is on the local machine or not. I know there are plenty of ways to identify the server\instance names of a SQL Server when connecting to it, but there are so many different ways a server name might be specified, I don't want to do the comparison myself between IP Addresses, machine names, (local), LocalHost, ., etc.

Is there something in SQL Server (any versions 2000+) where I can check if the instance is on the local machine or not without doing a comparison of my own?

Answer 1

SELECT Case when HOST_NAME()=
       Case When CharIndex('\',@@SERVERNAME)=0 then @@SERVERNAME else LEFT(@@SERVERNAME,CharIndex('\',@@SERVERNAME)-1) end
       then 'local' else 'remote' end

since @@SERVERNAME is defined on installation and could have been changed (even by sp_addserver) you would prefer:

SELECT Case when HOST_NAME()=SERVERPROPERTY('MachineName') then 'local' else 'remote' end