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
| — 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' | |
| ; |
Leave a Reply