Dynamically Obtain a SQL Server’s IP Address in T-SQL Code

I created the getSQL_IPaddr.sql procedure to find the IP address of a server running Microsoft SQL Server in T-SQL code. As Listing 1 shows, the getSQL_IPaddr.sql procedure uses the xp_cmdshell extended stored procedure to run the Ipconfig utility.

The getSQL_IPaddr.sql procedure runs Ipconfig, which displays its results following the format

Windows IP Configuration
Ethernet adapter Local Area Connection:
Connection-specific DNS Suffix: xxxxx.xxxx.xxxx.xxx
IP Address: xxx.xxx.xxx.xxx
Subnet Mask: xxx.xxx.xxx.xxx
Default Gateway: xxx.xxx.xxx.xxx

Because I want only the IP address, the getSQL_IPaddr.sql procedure enters the Ipconfig output in a temporary table, then extracts the IP address.

To use the getSQL_IPaddr.sql procedure, you need permission to run xp_cmdshell from the master. Here’s an example of how to call the procedure:

declare @ip varchar(40)exec sp_get_ip_address @ip outprint @ip