Get IP address from T-SQL.
Requires xp_cmdshell to be enabled. If this is a problem for your environment, consider
enabling it, run the proc, then disable it.
NOTE: if there is
more than one IP address, the output will indicate that:
10.1.10.92
(IPs found: 2)
In this event, you will need to add some code to
differentiate which one you need.
Use Admin
go
IF Object_id('dbo.GetIP') Is Not Null
DROP PROC
dbo.GetIP
go
CREATE PROC [dbo].[GetIP]
@IP varchar(30) OUTPUT,
@Debug bit = 0
AS
/* DATE AUTHOR REMARKS
2/15/13 PPaiva Initial creation.
USAGE
-- Get IP
DECLARE @ip varchar(30)
Exec GetIP @ip OUTPUT
SELECT @ip IP
-- Get IP with debug
DECLARE @ip varchar(30)
Exec GetIP @ip OUTPUT, 1
SELECT @ip IP
DEBUG
--Exec sp_configure 'xp_cmdshell', 1
--reconfigure
SELECT *
FROM ServerInfo
*/
SET NOCOUNT ON
DECLARE @s varchar(100),
@NumIPs int
SET @s = 'ipconfig'
CREATE TABLE #ipconfig(
ID int NOT
NULL IDENTITY(1, 1) PRIMARY KEY,
Col1 varchar(1000)
)
INSERT INTO #ipconfig
Exec xp_cmdshell @s
DECLARE @PosColon int
IF @Debug = 1
SELECT *
FROM #ipconfig
DELETE
FROM #ipconfig
WHERE Col1 Not Like '%ipv4%'
OR Col1 Is Null
SELECT @NumIPs = Count(*)
FROM #ipconfig
SELECT TOP 1 @PosColon = CharIndex(':', Reverse(Col1))
FROM #ipconfig
ORDER BY ID desc
SELECT TOP 1 @ip = LTrim(Right(Col1, @PosColon - 1))
FROM #ipconfig
ORDER BY ID desc
IF @NumIPs > 1
SET @ip = @ip + '
(IPs found: ' + Convert(varchar, @NumIPs) + ')'
IF @Debug = 1
SELECT @PosColon 'PosColon (reverse)'