mssql sqlserver 使用sql脚本获取sql 服务器名称和ip地址的三种方法分享


摘要:
下文讲述使用sql脚本获取sql引擎服务器的名称的IP地址的方法分享,如下所示:
实验环境:sql server 2008 r2


方法1:
通过SERVERPROPERTY获取MachineName属性获取服务器名称
或@@serverName全局变量获取服务器名称

 
SELECT SERVERPROPERTY('MachineName') as [服务器名称]
select @@SERVERNAME as [服务器名称]
select HOST_NAME()  as [客户端名称]

方法2:
使用xp_cmdshell运行cmd命令ipconfig获取服务器的相关信息

    --设置xp_cmdshell 属性,使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 
   
  if object_id('tempdb..#tmp') is not null drop table #tmp

create table #tmp(ipInfo nvarchar(300))

insert into #tmp(ipInfo)
exec master..xp_cmdshell'ipconfig'

delete from #tmp 
where ipinfo not like '%ip%'
or charindex('.',ipinfo) <=0 
or ipinfo is null 

select ipinfo
as [sqlserver服务器IP]  from #tmp 

truncate table  #tmp
drop     table  #tmp 

方法3:
通过查询系统存储过程 SYS.DM_EXEC_CONNECTIONS 获取服务器名称和IP

  SELECT  CONVERT(NVARCHAR(128),SERVERPROPERTY('SERVERNAME'))  as [服务器名称]
,LOCAL_NET_ADDRESS AS '服务器ip地址'
,CLIENT_NET_ADDRESS AS '客户端ip地址'
 FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID