如何从 SQL Server 2008 中的 ip 地址获取机器名称？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15873060/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get machine name from ip address in SQL Server 2008?
提问by Ahmed Ali
I have to get the machine name from IP address in SQL Server, is there anything that I could do to accomplish my task
我必须从 SQL Server 中的 IP 地址获取机器名称，我可以做些什么来完成我的任务
回答by Freelancer
DB:
D B：
IPAdd | MachineName
Query:
询问：
select MachineName from DBTable where IPAdd='yourIPAddress'
Try with:
尝试：
SELECT SERVERPROPERTY('MachineName')
Or
或者
SELECT
CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('protocol_type') AS protocol_type,
CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
CONNECTIONPROPERTY('client_net_address') AS client_net_address
Or
或者
SELECT @@SERVERNAME;
Refer:
参考：
http://msdn.microsoft.com/en-us/library/ms187944.aspx
http://msdn.microsoft.com/en-us/library/ms187944.aspx
Hope its helpful.
希望它有帮助。
回答by Remus Rusanu
What you want is called a Reverse DNS lookup. There are command line tools that can do it (nslookup.exe) or you can use the DnsQueryAPI. You have no business doing either from T-SQL so don't try to do it. Resolve the IP to name in the client.
您想要的称为反向 DNS 查找。有可以执行此操作的命令行工具 ( nslookup.exe) 或者您可以使用DnsQueryAPI。您与 T-SQL 没有任何关系，所以不要尝试这样做。将 IP 解析为客户端中的名称。
回答by steoleary
You can't do this natively in the SQL language, your best bet is to either:
您不能在 SQL 语言中本地执行此操作，您最好的选择是：
1/ shell out via xp-cmdshell and run nslookup, which will require some string manipulation to get the command right and then some cleaning up of the output to return the machine name
1/ 通过 xp-cmdshell 退出并运行 nslookup，这将需要一些字符串操作才能使命令正确，然后对输出进行一些清理以返回机器名称
2/ Write a C# CLR function that takes the IP address as an input and makes use of the Dns.GetHostEntry method to resolve and return the name.
2/ 编写一个 C# CLR 函数，该函数将 IP 地址作为输入并使用 Dns.GetHostEntry 方法来解析和返回名称。
See here for the documentation:
有关文档，请参见此处：
I wrote a really quick simple CLR function for getting the machine name from the IP Address is below, please note there is no error handling or input checking to make sure the IP is valid before trying to resolve it, and it won't give you the IP address if you pass it a hostname, but it can be easily modified to include all these things, it's just to give you an idea of how it could work:
我写了一个非常简单的 CLR 函数，用于从下面的 IP 地址获取机器名称，请注意没有错误处理或输入检查以确保在尝试解析 IP 之前是有效的，它不会给你如果您将 IP 地址传递给它一个主机名，则可以轻松修改它以包含所有这些内容，这只是为了让您了解它是如何工作的：
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString dnsResolve(String ipAddress)
{
IPHostEntry host = Dns.GetHostEntry(ipAddress);
return new SqlString(host.HostName.ToString());
}
}
回答by Nenad Zivkovic
I might be wrong but as far as I know, SQL Server can not do what you want. Resolving machine names from IP address is something your network's DNS server does.
我可能是错的，但据我所知，SQL Server 不能做你想做的事。从 IP 地址解析机器名称是您网络的 DNS 服务器所做的事情。
Best idea I come up with to get that info from SQL Server is using xp_cmdshellcommand to execute command prompt commands from SQL.
我想出的从 SQL Server 获取该信息的最佳想法是使用xp_cmdshell命令从 SQL 执行命令提示符命令。
xp_cmdshell 'NBTSTAT -A 10.10.10.10'
However, please note that xp_cmdshell needs to be enabled on your server first in order to work, and often it is not beacause of possible security issues. Read more about xp_cmdshell at http://msdn.microsoft.com/en-us/library/ms175046.aspx
但是，请注意 xp_cmdshell 需要首先在您的服务器上启用才能工作，而且通常不是因为可能的安全问题。在http://msdn.microsoft.com/en-us/library/ms175046.aspx阅读有关 xp_cmdshell 的更多信息
Also, result from this will be just like from command prompt and it will require some parsing to get exact machine name from it. Check this topic for more info: Get Results from XP_CMDSHELL
此外，由此产生的结果将与命令提示符类似，需要进行一些解析才能从中获取准确的机器名称。查看此主题以获取更多信息：从 XP_CMDSHELL 获取结果
I don't know your solution architecture and how you get the IP address, but if it is from some client side applications, it might be easier to find client's machine name executing SELECT HOST_NAME()from client's side query or with .NET and sending machine names directly.
我不知道您的解决方案体系结构以及您如何获取 IP 地址，但如果它来自某些客户端应用程序，则可能更容易找到SELECT HOST_NAME()从客户端查询或使用 .NET 执行并直接发送机器名称的客户端机器名称。
回答by Sky Diver
--Im using command line for checking host name after IP
declare @IP as varchar(15)
declare @cmd as varchar(1000)
set @IP='192.168.0.1'
SET @cmd = 'ping -a -n 1 ' + @IP
Create Table #Output (Output varchar(150) default(''))
INSERT INTO #Output
EXEC xp_cmdshell @cmd
Begin try
Select top 1 Replace(LEFT([Output],CHARINDEX('[', [output])-2),'Pinging ','') as HostName from #Output where Output is not null
End Try
Begin catch
Select 'Host name for:' + @IP +' could not be find'
End catch
drop table #Output
回答by khush
You can try this to get the name of your machine.
你可以试试这个来获取你机器的名字。
SELECT SERVERPROPERTY(N'MachineName');
May be above can serve your purpose.
可能以上可以达到您的目的。
回答by John Dean
This actually works: Modified answer from Sky Diver
这确实有效：Sky Diver 的修改后的答案
--Im using command line for checking host name after IP
declare @IP as varchar(15)
declare @cmd as varchar(1000)
set @IP='137.201.17.204'
SET @cmd = 'ping -a -n 1 ' + @IP
Create Table #Output (Output varchar(150) default(''))
INSERT INTO #Output
EXEC xp_cmdshell @cmd
Begin try
Select top 1 Replace(LEFT([Output],CHARINDEX('[', [Output])-2),'Pinging ','') as HostName from #Output where Output like 'Pinging%'
End Try
Begin catch
Select 'Host name for:' + @IP +' could not be find'
End catch
--drop table #Output
回答by Rajsri Vyshunnavi
Try this you will get a system Name.......
试试这个你会得到一个系统名称......
select HOST_NAME()
选择 HOST_NAME()
regards : Rajsri Vyshnnavi
问候 : Rajsri Vyshnnavi
