Please use below code:
Comments in Green explains the outcome of the code.
Please visit https://msdn.microsoft.com/en-us/library/cc645956.aspx for more information.
-- @@SERVERNAME provides the currently
configured local server name.
select SERVERPROPERTY
('ServerName') as 'Server Name'
-- What is the machine name? (Windows
computer name on which the server instance is running.)
select serverproperty
('MachineName') as 'Machine Name'
-- What is the machine NETBIOS Name?
(Note: NetBIOS name of the local computer on which the instance of SQL Server
is currently running.)
select serverproperty
('ComputerNamePhysicalNetBIOS') as 'NETBIOS'
-- Process ID of SQL Server service
select serverproperty
('ProcessID') as 'Process ID'
-- Information around Resource Database
select SERVERPROPERTY
('Resourceversion') as 'Resource DB Version', SERVERPROPERTY ('ResourceLastUpdateDateTime') as 'Last date and time that the
Resource database was last updated.'
-- Is the instance clustered? (1 For
Clustered, 0 for Non Clustered)
select serverproperty
('isClustered') as 'Clustered or Not'
-- Is the instance a Local DB?
select serverproperty
('IsLocalDB') as '1 = Yes, 0 = No'
-- Does server support in memory OLTP?
select serverproperty
('IsXTPSupported') as '1 = Yes, 0 = No'
-- Does server support Always On?
select serverproperty
('IsHADREnabled') as '1 = Enabled, 0 = Not Enabled,
Null = Not Applicable'
-- Status of Always On?
select serverproperty
('HadrManagerStatus') as '1 = Started & Running, 2 =
Not Started & Failed, 0 = Not Started & Pending Communication, Null = Not Applicable'
-- Default data path
select serverproperty
('InstanceDefaultDataPath')
-- Default log path
select serverproperty
('InstanceDefaultLogPath')
-- Full text related information
select serverproperty
('IsFullTextInstalled') as '1 = Yes, 0 = No, Null = Not
Applicable'
-- Authentication Mode
select serverproperty
('IntegratedSecurityOnly') as '1 = Integrated security
(Windows Authentication), 2 = Mixed Mode, Null = Not Applicable'
-- Filestram related details (0 = Disabled, 1 = Enabled for TSQL access, 2 = Enables FILESTREAM for Transact-SQL and Win32 streaming access)
select SERVERPROPERTY ('FilestreamConfiguredLevel'), SERVERPROPERTY ('FilestreamEffectiveLevel'), SERVERPROPERTY ('FilestreamShareName')
Comments