Get SQL Server Instance TCP ports configuration from registry and standardized services names

get-services.sql

WITH cte AS
(
	SELECT
		CASE
		WHEN	PATINDEX('SQL Server (%', servicename) > 0
		THEN	'Engine'
		WHEN	PATINDEX('SQL Server Agent (%', servicename) > 0
		THEN	'Agent'
		WHEN	PATINDEX('SQL Full-text Filter Daemon Launcher (%', servicename) > 0
		THEN	'FullText'
		WHEN	PATINDEX('SQL Server Launchpad (%', servicename) > 0
		THEN	'Launchpad'
	END AS ServiceType
,	REPLACE(
		REPLACE(
			REPLACE(
				REPLACE(
					REPLACE(servicename, 'SQL Server (', '')
				,'SQL Server Agent (', '')
			, 'SQL Full-text Filter Daemon Launcher (', '')
		, 'SQL Server Launchpad (', '')
	,')','') AS InstanceName
,	servicename AS DisplayName
,	service_account AS StartName
	FROM	sys.dm_server_services
)
SELECT	
	SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ComputerName
,	CASE
	WHEN ServiceType = 'Engine' THEN
		CASE WHEN InstanceName = 'MSSQLSERVER' THEN 'MSSQLSERVER' ELSE 'MSSQL$' + InstanceName END
	WHEN ServiceType = 'Agent' THEN
		CASE WHEN InstanceName = 'MSSQLSERVER' THEN 'SQLSERVERAGENT' ELSE 'SQLAgent$' + InstanceName END
	WHEN ServiceType = 'FullText' THEN
		CASE WHEN InstanceName = 'MSSQLSERVER' THEN 'MSSQLFDLauncher' ELSE 'MSSQLFDLauncher$' + InstanceName END
	WHEN ServiceType = 'Launchpad' THEN
		CASE WHEN InstanceName = 'MSSQLSERVER' THEN 'MSSQLLAUNCHPAD' ELSE 'MSSQLLaunchpad$' + InstanceName END
	END AS ServiceName
,	*
FROM	cte;

get-tcp-ports.sql

SELECT
	SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ComputerName
,	ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLSERVER') AS InstanceName
,	CASE
		WHEN registry_key LIKE '%\SuperSocketNetLib\Tcp\%' THEN N'Instance Port'
		WHEN registry_key LIKE '%\AdminConnection\%' THEN N'DAC'
	END AS PortService
,	value_name AS PortAssignmentType
,	REPLACE(CAST(value_data AS varchar) COLLATE SQL_Latin1_General_CP1_CI_AS,CHAR(0),'') AS PortNumber
FROM	sys.dm_server_registry
WHERE
	(	registry_key LIKE '%MSSQLServer\SuperSocketNetLib\Tcp\%'
		OR registry_key LIKE '%MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp'
	)
  AND value_name IN ('TcpPort', 'TcpDynamicPorts')
  AND value_data NOT IN ('', '0');