dm_tcp_listener_states DMV Altered to be Readable

The sys.dm_tcp_listener_states DMV was introduced in version SQL Server 2012.

This is a great DMV because the SQL Configuration Manager does not have to opened to find these values.  However some of the columns are not readable with out referencing the MSDN web site to find what the default values for the columns IP_ADDRESS and IS_IPV4:

sys.dm_tcp_listener_states (Transact-SQL)

sys.dm_tcp_listener_states

Below is the sys.dm_tcp_listener_states DMV, which I altered to make the results more readable:

select listener_id,
 CASE ip_address when '::' THEN 'IPv6 Default All'
 when '0.0.0.0' THEN 'IPv4 Default All'
 ELSE ip_address END AS 'IP_ADDRESS',
 CASE is_ipv4 when 1 THEN 'IPv4' ELSE 'IPv6' END AS 'IP_TYPE',
 port,
 type_desc,
 state_desc,
 start_time
 from sys.dm_tcp_listener_states
 WITH (NOLOCK) OPTION (RECOMPILE);

Below the columns for ip_address and is_ipv4 have a more readable value:

dm_tcp_listener_states_01

If the SQL Configuration Manager is altered to exclude some IP addresses, then those updated values would be displayed in the IP_ADDRESS column:

dm_tcp_listener_states_02

 

Leave a Reply