How do you return the connection name via TSQL? This is visible in SSMS via the properties window. For example if you have a DNS alias "ProductionServer" for a machine named "SRVR0001" and further have a local server registration for this named "Live Production" and then
1) Open a new query providing a Server name of "ProductionServer" then the connection name will be "ProductionServer"
2) Right click the local registration and select new query, the connection name will be "Live Production"
And of course in both instances select @@SERVERNAME
will return SRVR0001.
This is similar, but different, to these questions:
https://stackoverflow.com/questions/16088151/how-to-find-server-name-of-sql-server-management-studio
https://stackoverflow.com/questions/129861/how-can-i-query-the-name-of-the-current-sql-server-database-instance
I anticipate the answer may well be that this is a local property held by SSMS, and thus can not be returned by TSQL run on SQL Server.
Best Answer
The comment left by user sepupic is correct:
The only places I might have expected to see this would be the "connection" related DMVs, for instance if SQL Server kept track of the connection name used to connect to it (similar to how it keeps track of the client host name, set options used, etc).
However, it's not present in any of the main ones I checked:
sys.dm_exec_connections
sys.dm_exec_requests
sys.dm_exec_sessions
And this makes sense, especially with registrations where the client has to translate those names before creating the connection anyway.