The other day I was asked to provide the port number that a SQL Server instance was listening on. As luck would have it, the log file from the last time SQL Server started had rolled off so I was left with either using remote desktop to log onto the server and all the risk that includes or finally figuring out how to query it from the DMVs. I opted for the second option.
I did some digging, opening any of the system views that looked promising. Unfortunately, I was not having much luck. I was about to give up when I remembered that sys.dm_exec_connections will tell what IP address and port. If it had the endpoint_id to join to then I could create my own version of sys.endpoints with the information I need. A quick look at the view showed it had the fields I needed.
Here is the query I came up with:
I hope you find this script helpful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.