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?

For 2005+ maybe SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id= @@SPID. Haven't looked into whether that would be reliable or not. — Martin Smith
– Martin Smith, Commented Jan 7, 2013 at 13:52
possible duplicate of How to tell if SQL Server is local or remote? That one gives you an alternative to @@SERVERNAME — Martin Smith
– Martin Smith, Commented Jan 7, 2013 at 15:07
@MartinSmith: Definitely not a duplicate of that question: The OP of that other question wants to compare host names (see "edit 1" in question body), while the OP of the present question wants to avoid doing exactly that. — stakx - no longer contributing
– stakx - no longer contributing, Commented Oct 7, 2014 at 7:13

bummi · Accepted Answer · 2013-01-07 15:17:43Z

4

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

edited Jan 7, 2013 at 15:17

answered Jan 7, 2013 at 14:17

bummi

27.4k14 gold badges67 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jerry Dodge Over a year ago

So far I've tested with MyMachineName, (local), localhost, ., 192.168.1.100, 192.168.1.100, 1433, and it works great. Now to test on a named instance...

Jerry Dodge Over a year ago

Did not work on a named instance MyMachineName\Instance - returns remote

bummi Over a year ago

@Jerry Dodge Can't reproduce what's HOST_NAME() and @@SERVERNAME output?

Jerry Dodge Over a year ago

Uhmmm... very confused here, @@SERVERNAME is returning the name of a completely different machine, which I'm obviously not connected to ... ?????

Jerry Dodge Over a year ago

Ohhhhhh the server name which it's returning in @@SERVERNAME is my OLD machine name - I've changed the machine name since then. Strange why this would still return the old machine name?

benka · Accepted Answer · 2014-10-07 07:14:41Z

0

SELECT CASE WHEN CAST(SERVERPROPERTY('MachineName') AS VARCHAR(100)) = CAST(HOST_NAME() AS VARCHAR(100)) THEN 'LOCAL' 
ELSE 'REMOTE' 
END

edited Oct 7, 2014 at 7:14

benka

4,74235 gold badges50 silver badges58 bronze badges

answered Oct 7, 2014 at 7:06

Alain

1

Collectives™ on Stack Overflow

Check if a SQL Server instance is on local computer?

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related