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