Small query for some SQL Server client and server information
Posted by jpluimers on 2020/08/24
Sometimes in a less paved SQL Server environment you need a quick way to gather information on both the client and server. I assembled this query from various sources to help with that. It runs with few privileges (hence the use of the various *property functions):
-- https://dev-doc.blogspot.com/2012/08/ms-sql-2008-client-ip-address-on-shared.html
-- https://blog.sqlauthority.com/2009/05/26/sql-server-find-hostname-and-current-logged-in-user-name/
-- https://blog.sqlauthority.com/2015/07/13/sql-server-how-to-change-server-name/
-- https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6720817d-120f-4099-bf0e-e97fd2e26848/how-to-get-host-name-and-sql-instance-name-by-tsql?forum=transactsql#fc9e6b84-0264-424a-8aef-d03b0de6fade
select
-- https://docs.microsoft.com/en-us/sql/t-sql/functions/connectionproperty-transact-sql?view=sql-server-2017
CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('protocol_type') AS protocol_type,
CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
CONNECTIONPROPERTY('client_net_address') AS client_net_address,
HOST_NAME() AS client_hostname,
SUSER_NAME() LoggedInUser,
@@servername AS 'ServerName\InstanceName',
-- https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-2017
SERVERPROPERTY('ServerName') AS 'ServerName',
SERVERPROPERTY('MachineName') AS 'Windows_MachineName',
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS 'NetBIOS_Name',
SERVERPROPERTY('instanceName') AS 'InstanceName',
SERVERPROPERTY('IsClustered') AS 'IsClustered',
SERVERPROPERTY('Edition') AS 'Edition',
-- https://docs.microsoft.com/en-us/sql/t-sql/functions/version-transact-sql-configuration-functions?view=sql-server-2017
@@Version as 'Full_Server_Version'
;
Based on parts from:
- [WayBack] Заметки разработчика: MS SQL 2008: Client IP address on shared hosting
- [WayBack] SQL SERVER – Find Hostname and Current Logged In User Name – SQL Authority with Pinal Dave
- [WayBack] SQL SERVER – How to Change Server Name? – SQL Authority with Pinal Dave
- [WayBack] How to get Host name and SQL Instance Name by T-SQL
Features used:
- [WayBack] CONNECTIONPROPERTY (Transact-SQL) | Microsoft Docs
- [WayBack] HOST_NAME (Transact-SQL) | Microsoft Docs
- [WayBack] SUSER_NAME (Transact-SQL) | Microsoft Docs
- [WayBack] SERVERPROPERTY (Transact-SQL) | Microsoft Docs
- [WayBack] @@VERSION (Transact-SQL) | Microsoft Docs
–jeroen
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| — https://dev-doc.blogspot.com/2012/08/ms-sql-2008-client-ip-address-on-shared.html | |
| — https://blog.sqlauthority.com/2009/05/26/sql-server-find-hostname-and-current-logged-in-user-name/ | |
| — https://blog.sqlauthority.com/2015/07/13/sql-server-how-to-change-server-name/ | |
| — https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6720817d-120f-4099-bf0e-e97fd2e26848/how-to-get-host-name-and-sql-instance-name-by-tsql?forum=transactsql#fc9e6b84-0264-424a-8aef-d03b0de6fade | |
| select | |
| — https://docs.microsoft.com/en-us/sql/t-sql/functions/connectionproperty-transact-sql?view=sql-server-2017 | |
| CONNECTIONPROPERTY('net_transport') AS net_transport, | |
| CONNECTIONPROPERTY('protocol_type') AS protocol_type, | |
| CONNECTIONPROPERTY('auth_scheme') AS auth_scheme, | |
| CONNECTIONPROPERTY('local_net_address') AS local_net_address, | |
| CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port, | |
| CONNECTIONPROPERTY('client_net_address') AS client_net_address, | |
| HOST_NAME() AS client_hostname, | |
| SUSER_NAME() LoggedInUser, | |
| @@servername AS 'ServerName\InstanceName', | |
| — https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-2017 | |
| SERVERPROPERTY('ServerName') AS 'ServerName', | |
| SERVERPROPERTY('MachineName') AS 'Windows_MachineName', | |
| SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS 'NetBIOS_Name', | |
| SERVERPROPERTY('instanceName') AS 'InstanceName', | |
| SERVERPROPERTY('IsClustered') AS 'IsClustered', | |
| SERVERPROPERTY('Edition') AS 'Edition', | |
| — https://docs.microsoft.com/en-us/sql/t-sql/functions/version-transact-sql-configuration-functions?view=sql-server-2017 | |
| @@Version as 'Full_Server_Version' | |
| ; |
The Wiert Corner - irregular stream of stuff 
Leave a Reply