SQL - Query to get server's IP address

Question

Is there a query in SQL Server 2005 I can use to get the server's IP or name?

Answer 1

SELECT      CONNECTIONPROPERTY('net_transport') AS net_transport,    CONNECTIONPROPERTY('protocol_type') AS protocol_type,    CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,    CONNECTIONPROPERTY('local_net_address') AS local_net_address,    CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,    CONNECTIONPROPERTY('client_net_address') AS client_net_address  

The code here Will give you the IP Address;

This will work for a remote client request to SQL 2008 and newer.

If you have Shared Memory connections allowed, then running above on the server itself will give you

• "Shared Memory" as the value for 'net_transport', and
• NULL for 'local_net_address', and
• '<local machine>' will be shown in 'client_net_address'.

'client_net_address' is the address of the computer that the request originated from, whereas 'local_net_address' would be the SQL server (thus NULL over Shared Memory connections), and the address you would give to someone if they can't use the server's NetBios name or FQDN for some reason.

I advice strongly against using this answer. Enabling the shell out is a very bad idea on a production SQL Server.

Answer 2

You can get the[hostname]\[instancename] by:

SELECT @@SERVERNAME; 

To get only the hostname when you have hostname\instance name format:

SELECT LEFT(ltrim(rtrim(@@ServerName)), Charindex('\', ltrim(rtrim(@@ServerName))) -1) 

Alternatively as @GilM pointed out:

SELECT SERVERPROPERTY('MachineName') 

You can get the actual IP address using this:

create Procedure sp_get_ip_address (@ip varchar(40) out) as begin Declare @ipLine varchar(200) Declare @pos int set nocount on           set @ip = NULL           Create table #temp (ipLine varchar(200))           Insert #temp exec master..xp_cmdshell 'ipconfig'           select @ipLine = ipLine           from #temp           where upper (ipLine) like '%IP ADDRESS%'           if (isnull (@ipLine,'***') != '***')           begin                  set @pos = CharIndex (':',@ipLine,1);                 set @ip = rtrim(ltrim(substring (@ipLine ,                 @pos + 1 ,                 len (@ipLine) - @pos)))            end  drop table #temp set nocount off end  go  declare @ip varchar(40) exec sp_get_ip_address @ip out print @ip 

Source of the SQL script.