Server Details

To Get Server Details

select CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) AS 'Server Name',
'' as 'IP Address',
(select @@version) as SQL_Name,
CAST(SERVERPROPERTY('Edition') AS VARCHAR(200)) AS 'Server Edition',
CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(15)) AS 'Service Pack',
Getdate() as 'As On',
''As Purpose,
CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR(17)) AS 'Cluster Name'

---

--- IsClustered
SELECT CONVERT(char(20), SERVERPROPERTY('IsClustered'));
--- Physical Name
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
--- Logical Name
SELECT SERVERPROPERTY('ComputerNameLogicalNetBIOS');
-- Detrimin Bit version
select serverproperty('edition')
-- Detrimin Product Version
SELECT SERVERPROPERTY('ProductVersion')
-- Detrimin License
SELECT SERVERPROPERTY('LicenseType') AS [LicenseType]
SELECT SERVERPROPERTY('NumLicenses') AS [NumLicenses]

---

--- All Details about OS
exec master..xp_cmdshell 'systeminfo'
exec xp_msver

--- Server Details
select * from sys.dm_os_sys_info


--- To Get The OS Version
master..xp_msver
master..xp_cmdshell 'netsh diag SHOW os /p'
select right(@@version, 44)

---

---- Important Query for Server Details
select @@servername
select @@version
SELECT SERVERPROPERTY ('MachineName') As ServerName
SELECT SERVERPROPERTY ('Edition') As Edition
SELECT SERVERPROPERTY ('ProductVersion')As ProductVersion
SELECT SERVERPROPERTY('ProductLevel')as ProductLevel
SELECT CONVERT(char(20), SERVERPROPERTY('BuildClrVersion'));
SELECT CONVERT(char(20), SERVERPROPERTY('Collation'));
SELECT CONVERT(char(20), SERVERPROPERTY('CollationID'));
SELECT CONVERT(char(20), SERVERPROPERTY('ComparisonStyle'));
SELECT CONVERT(char(20), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'));
SELECT CONVERT(char(20), SERVERPROPERTY('Edition'));
SELECT CONVERT(char(20), SERVERPROPERTY('EditionID'));
SELECT CONVERT(char(20), SERVERPROPERTY('EngineEdition'));
SELECT CONVERT(char(20), SERVERPROPERTY('InstanceName'));
SELECT CONVERT(char(20), SERVERPROPERTY('IsClustered'));
SELECT CONVERT(char(20), SERVERPROPERTY('IsFullTextInstalled'));
SELECT CONVERT(char(20), SERVERPROPERTY('IsIntegratedSecurityOnly'));
SELECT CONVERT(char(20), SERVERPROPERTY('IsSingleUser'));
SELECT CONVERT(char(20), SERVERPROPERTY('LCID'));
SELECT CONVERT(char(20), SERVERPROPERTY('LicenseType'));
SELECT CONVERT(char(20), SERVERPROPERTY('MachineName'));
SELECT CONVERT(char(20), SERVERPROPERTY('NumLicenses'));
SELECT CONVERT(char(20), SERVERPROPERTY('ProcessID'));
SELECT CONVERT(char(20), SERVERPROPERTY('ProductVersion'));
SELECT CONVERT(char(20), SERVERPROPERTY('ProductLevel'));
SELECT CONVERT(char(20), SERVERPROPERTY('ResourceLastUpdateDateTime'));
SELECT CONVERT(char(20), SERVERPROPERTY('ResourceVersion'));
SELECT CONVERT(char(20), SERVERPROPERTY('ServerName'));
SELECT CONVERT(char(20), SERVERPROPERTY('SqlCharSet'));
SELECT CONVERT(char(20), SERVERPROPERTY('SqlCharSetName'));
SELECT CONVERT(char(20), SERVERPROPERTY('SqlSortOrder'));
SELECT CONVERT(char(20), SERVERPROPERTY('SqlSortOrderName'));
SELECT CONVERT(char(20), SERVERPROPERTY('FilestreamShareName'));
SELECT CONVERT(char(200), SERVERPROPERTY('FilestreamConfiguredLevel'));
SELECT SERVERPROPERTY('FilestreamEffectiveLevel')

---