-- 远程连接服务器查询
SELECT local_net_address , local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@spid

-- 2008以后可以执行：
select
 SERVERPROPERTY('MachineName') AS [hostname],
isnull(SERVERPROPERTY('InstanceName'),'MSSQLSERVER') AS [实例名称],
  CONNECTIONPROPERTY('local_net_address') [IP地址],
  CONNECTIONPROPERTY('local_tcp_port') [端口地址]

-- 服务器本地读取 get Port
DECLARE       @portNumber   NVARCHAR(10)
EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        = N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\IPAll',
@value_name = N'TcpPort',
@value      = @portNumber OUTPUT

SELECT [Port Number] = @portNumber

-- get IP, 服务器本地读取
DECLARE       @portNumber   NVARCHAR(30)
EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        = N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\IP10',
@value_name = N'IpAddress',
@value      = @IP OUTPUT

SELECT [IP] = @IP

---------------------------------
-- 连接本地服务器通过信任TCP连接获取远程服务器IP地址，需要有信任权限

-- 修改Ad Hoc Distributed Queries设置为1

-- SELECT value FROM SYS.CONFIGURATIONS where name='Ad Hoc Distributed Queries';
DECLARE @value      sql_variant
SELECT @value=value FROM SYS.CONFIGURATIONS where name='Ad Hoc Distributed Queries';
select @value

-- 保存当前设置
-- drop table #val
-- if object_id('#val','u') is null
if not exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#VAL') and type='U')
begin
create table #val (value varchar(100))
insert into #val(value) select CONVERT(NVARCHAR(20),value) FROM SYS.CONFIGURATIONS where name='Ad Hoc Distributed Queries'
end

exec SP_CONFIGURE 'show advanced options',1
go
RECONFIGURE

EXEC sp_configure 'Ad Hoc Distributed Queries', 1  
GO  
RECONFIGURE;  
GO

-- 连接本地服务器建立远程信任连接实现分布式查询：

DECLARE @ip_address       varchar(15)
DECLARE @tcp_port         int 
DECLARE @connectionstring nvarchar(max) 
DECLARE @parm_definition  nvarchar(max)
DECLARE @command          nvarchar(max)

SET @connectionstring = N'Server=tcp:' + @@SERVERNAME + ';Trusted_Connection=yes;'
SET @parm_definition  = N'@ip_address_OUT varchar(15) OUTPUT
                        , @tcp_port_OUT   int         OUTPUT';

SET @command          = N'SELECT  @ip_address_OUT = a.local_net_address,
                                  @tcp_port_OUT   = a.local_tcp_port
                          FROM OPENROWSET(''SQLNCLI''
                                 , ''' + @connectionstring + '''
                                 , ''SELECT local_net_address
                                          , local_tcp_port
                                     FROM sys.dm_exec_connections
                                     WHERE session_id = @@spid
                                   '') as a'

EXEC SP_executeSQL @command
                 , @parm_definition
                 , @ip_address_OUT = @ip_address OUTPUT
                 , @tcp_port_OUT   = @tcp_port OUTPUT;


-- 获得服务器IP
SELECT @ip_address, @tcp_port



-- 恢复设置
DECLARE @val      varchar(100)
select @val=value from #val;
select @val

EXEC sp_configure 'Ad Hoc Distributed Queries', @val  
GO  
RECONFIGURE;  
GO


exec SP_CONFIGURE 'show advanced options',0
go
RECONFIGURE

-- 远程通过TCP方式连接到服务器：

SELECT SERVERNAME = CONVERT(NVARCHAR(128),SERVERPROPERTY('SERVERNAME'))  
,LOCAL_NET_ADDRESS AS 'IPAddressOfSQLServer' 
,CLIENT_NET_ADDRESS AS 'ClientIPAddress' 
--, NETBIOS = CONVERT(NVARCHAR(128),SERVERPROPERTY('COMPUTERNAMEPHYSICALNETBIOS'))  
--, [MAXDOP] = (SELECT VALUE_IN_USE FROM SYS.CONFIGURATIONS WHERE NAME='MAX DEGREE OF PARALLELISM')   
--, SQLMEMORY = (SELECT VALUE_IN_USE FROM SYS.CONFIGURATIONS WHERE NAME='MAX SERVER MEMORY (MB)')  
--, EDITION = CONVERT(NVARCHAR(128),SERVERPROPERTY('EDITION'))  
---, COLLATION = CONVERT(NVARCHAR(128),SERVERPROPERTY('COLLATION'))  
--, ISCLUSTERED = CONVERT(BIT,SERVERPROPERTY('ISCLUSTERED'))  
--, ISFULLTEXTINSTALLED = CONVERT(BIT,SERVERPROPERTY('ISFULLTEXTINSTALLED'))  
--, ISINTEGRATEDSECURITYONLY = CONVERT(BIT,SERVERPROPERTY('ISINTEGRATEDSECURITYONLY'))  
--, FILESTREAMCONFIGUREDLEVEL = CONVERT(TINYINT,SERVERPROPERTY('FILESTREAMCONFIGUREDLEVEL'))  
--, FILESTREAMEFFECTIVELEVEL = CONVERT(TINYINT,SERVERPROPERTY('FILESTREAMEFFECTIVELEVEL'))  
--, PRODUCTVERSION = CONVERT(NVARCHAR(128),SERVERPROPERTY('PRODUCTVERSION'))  
--, SQLCHARSETNAME = CONVERT(NVARCHAR(128),SERVERPROPERTY('SQLCHARSETNAME'))  
--, SQLSORTORDERNAME = CONVERT(NVARCHAR(128),SERVERPROPERTY('SQLSORTORDERNAME')) 
 FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID

 select serverproperty('Edition')   as [mssql版本名称] 
 select serverproperty('InstanceName') as [mssql实例名称] 
 select serverproperty('IsClustered') as [是否配置故障转移实例]  
 select serverproperty('IsFullTextInstalled') as [是否安装全文组件]  
 select serverproperty('IsIntegratedSecurityOnly') as [是否集成安全模式]  
 select serverproperty('IsSingleUser') as [是否单用户模式检测] 
 select serverproperty('IsSyncWithBackup') as [数据库是否为发布或分发数据库]  
 select serverproperty('LicenseType') as [许可模式]  
 select serverproperty('MachineName') as [DB服务器机器名称]
 select serverproperty('NumLicenses') as [DB服务器上许可数量]
 select serverproperty('ProcessID')   as [服务器引擎在操作系统中的进程ID]
 select serverproperty('ProductVersion') as [mssql服务器软件的版本信息] 
 select serverproperty('ProductLevel')  as [db服务器软件版本级别]  
 select serverproperty('ServerName') as [返回Db服务器机器名称]