Query to get Hostname,instancename,port number

• Steve Dell

  SSCarpal Tunnel

  Points: 4623

  More actions

  March 21, 2011 at 10:38 am

  #236525

  Hi Can any one shade light on getting the hostname,Instancename and portnumber for each instance for the following query? Thanks in advance

  DECLARE @GetInstances TABLE

  (Value VARCHAR(30),

  InstanceNames VARCHAR(20),

  Data VARCHAR(30))

  INSERT INTO @GetInstances

  EXECUTE xp_regread

  @rootkey = 'HKEY_LOCAL_MACHINE',

  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',

  @value_name = 'InstalledInstances'

  SELECT CAST(serverproperty('ComputerNamePhysicalNetBIOS')AS VARCHAR(10)) AS ServerName,

  CASE

  WHEN InstanceNames = 'MSSQLSERVER'

  THEN CAST(serverproperty('ComputerNamePhysicalNetBIOS') AS VARCHAR(10))

  ELSE (CAST(serverproperty('ComputerNamePhysicalNetBIOS') AS VARCHAR(10))+'\'+ InstanceNames)

  END AS InstanceName from @GetInstances

• Eddie Wuerch

  SSChampion

  Points: 12541

  More actions

  March 21, 2011 at 2:25 pm

  #1301582

  I generally use info from sys.dm_exec_connections to get that info:

  SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ServerName,

  @@SERVERNAME AS FullInstanceName, @@SERVICENAME AS InstanceName,

  local_net_address AS InstanceIPAddress, local_tcp_port AS InstancePort

  FROM sys.dm_exec_connections WHERE session_id = @@spid

  This query displays the IP address and port to which I connected in order to run the query.

  Because the info is exposed as a result of an inbound TCP connection, do not run the query directly on the server, which would use Shared Memory to connect, instead of TCP/IP.

  -Eddie

  Eddie Wuerch
  MCM: SQL