SQL statement to query the SQL Server name and IP address, SQL Server

SQL statement to query the SQL Server name and IP address, SQL Server

Get Server Name:

SELECT SERVERPROPERTY('MachineName')select @@SERVERNAMEselect HOST_NAME()

To obtain the IP address, run the ipconfig command in xp_cmdshell:

-- Enable xp_cmdshell exec sp_configure 'show advanced options', 1 reconfigure with override exec sp_configure 'xp _ cmdshell', 1 reconfigure with override exec sp_configure 'show advanced options ', 0 reconfigure with override go begin declare @ ipline varchar (200) declare @ pos int declare @ ip varchar (40) set nocount on set @ ip = null if object_id ('tempdb .. # temp ') is not null drop table # temp create table # temp (ipline varchar (200) insert # temp exec master .. xp_cmdshell 'ipconfig' select @ ipline = ipline from # temp where upper (ipline) like '% IPv4 address %' -- pay attention to this, if @ ipline is not null begin set @ pos = charindex (':', @ ipline, 1 ); set @ ip = rtrim (ltrim (substring (@ ipline, @ pos + 1, len (@ ipline)-@ pos ))) end select distinct (rtrim (ltrim (substring (@ ipline, @ pos + 1, len (@ ipline)-@ pos )))) as ipaddress from # temp drop table # temp set nocount off end go

However, xp_mongoshell is not allowed due to security issues in many cases. You can query SYS. DM_EXEC_CONNECTIONS:

SELECT SERVERNAME = CONVERT(NVARCHAR(128),SERVERPROPERTY('SERVERNAME')) ,LOCAL_NET_ADDRESS AS 'IPAddressOfSQLServer',CLIENT_NET_ADDRESS AS 'ClientIPAddress' FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID