这篇文章主要介绍了SQL语句实现查询SQL Server服务器名称和IP地址,本文分别给出查询语句,需要的朋友可以参考下
获取服务器名称:
1 2 3 |
SELECT SERVERPROPERTY( ’MachineName’ ) select @@SERVERNAME select HOST_NAME() |
获取IP地址可以使用xp_cmdshell执行ipconfig命令:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
--开启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 地址%’ --这里需要注意一下,系统不同这里的匹配值就不同 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 |
但是很多情况下由于安全问题是不允许使用xp_cmdshell,可以通过查询SYS.DM_EXEC_CONNECTIONS :
1 2 3 4 |
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 |
评论 (0)