Find Hostname

SELECT name from sys.servers WHERE server_id = 0

Find MSSQL Instance Info

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [Machine Name]
   ,SERVERPROPERTY('InstanceName') AS [Instance Name]
   ,LOCAL_NET_ADDRESS AS [IP Address Of SQL Server]
   ,CLIENT_NET_ADDRESS AS [IP Address Of Client]
 FROM SYS.DM_EXEC_CONNECTIONS
 WHERE SESSION_ID = @@SPID

Find Server IP

--To find out server IP address, execute this following script:

--To find the client IP address
SELECT CLIENT_NET_ADDRESS AS [IP Address Of Client]
FROM SYS.DM_EXEC_CONNECTIONS
WHERE SESSION_ID = @@SPID


--To find the server IP address
SELECT TOP(1) c.local_net_address 'Server IP'
FROM sys.dm_exec_connections AS c
WHERE c.local_net_address IS NOT NULL;