在SQL Server2005中是否有一个查询可以用来获取服务器的IP或名称?
发布于 2008-09-26 21:41:12
您可以通过以下方式获取thehostname[instancename]:
SELECT @@SERVERNAME;
要在使用hostname\instance name格式时仅获取主机名,请执行以下操作:
SELECT LEFT(ltrim(rtrim(@@ServerName)), Charindex('\', ltrim(rtrim(@@ServerName))) -1)
或者,正如@GilM指出的那样:
SELECT SERVERPROPERTY('MachineName')
您可以使用以下命令获取实际的IP地址:
create Procedure sp_get_ip_address (@ip varchar(40) out)
as
begin
Declare @ipLine varchar(200)
Declare @pos int
set nocount on
set @ip = NULL
Create table #temp (ipLine varchar(200))
Insert #temp exec master..xp_cmdshell 'ipconfig'
select @ipLine = ipLine
from #temp
where upper (ipLine) like '%IP ADDRESS%'
if (isnull (@ipLine,'***') != '***')
begin
set @pos = CharIndex (':',@ipLine,1);
set @ip = rtrim(ltrim(substring (@ipLine ,
@pos + 1 ,
len (@ipLine) - @pos)))
end
drop table #temp
set nocount off
end
go
declare @ip varchar(40)
exec sp_get_ip_address @ip out
print @ip
发布于 2012-03-09 02:26:17
服务器可能有多个正在侦听的IP地址。如果您的连接被授予VIEW SERVER STATE server权限,则可以运行此查询以获取已连接到SQL Server的地址:
SELECT dec.local_net_address
FROM sys.dm_exec_connections AS dec
WHERE dec.session_id = @@SPID;
该解决方案不需要您通过xp_cmdshell向操作系统提供外壳,这是一种应该在生产服务器上禁用(或至少是严格保护)的技术。它可能要求您将VIEW SERVER STATE授予适当的登录,但这比运行xp_cmdshell的安全风险小得多。
GilM提到的服务器名称的技术是首选的技术:
SELECT SERVERPROPERTY(N'MachineName');
发布于 2017-11-07 03:11:15
--试试这个脚本,它能满足我的需要。重新格式化以读取它。
SELECT
SERVERPROPERTY('ComputerNamePhysicalNetBios') as 'Is_Current_Owner'
,SERVERPROPERTY('MachineName') as 'MachineName'
,case when @@ServiceName =
Right (@@Servername,len(@@ServiceName)) then @@Servername
else @@servername +' \ ' + @@Servicename
end as '@@Servername \ Servicename',
CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
dec.local_tcp_port,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
dec.local_net_address as 'dec.local_net_address'
FROM sys.dm_exec_connections AS dec
WHERE dec.session_id = @@SPID;
https://stackoverflow.com/questions/142142
复制相似问题