GetIP

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)'