SQL Script (T-SQL) to find SQL Server details

In order to get or find SQL Server details, run below script :

CREATE TABLE #ServerDetails(ID int,  Name  sysname, Internal_Value int, Value nvarchar(512))

INSERT #ServerDetails EXEC master.dbo.xp_msver
DECLARE @InstanceName nvarchar(50)
DECLARE @value VARCHAR(100)
DECLARE @RegKey_InstanceName nvarchar(500)
DECLARE @RegKey nvarchar(500)
DECLARE @AuditLevel int
DECLARE @DataDirectory nvarchar(500)
DECLARE @LogDirectory nvarchar(500)
DECLARE @BackupDirectory nvarchar(500)

SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),'MSSQLSERVER'))if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8

BEGIN
SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey_InstanceName,
  @value_name = @InstanceName,
  @value = @value OUTPUT

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\'     

EXEC master..xp_regread 
  @rootkey='HKEY_LOCAL_MACHINE',
  @key=@RegKey,
  @value_name='AuditLevel',
  @value=@AuditLevel OUTPUT

EXEC master..xp_regread 
  @rootkey='HKEY_LOCAL_MACHINE',
  @key=@RegKey,
  @value_name='DefaultData',
  @value=@DataDirectory OUTPUT


EXEC master..xp_regread 
  @rootkey='HKEY_LOCAL_MACHINE',
  @key=@RegKey,
  @value_name='DefaultLog',
  @value=@LogDirectory OUTPUT


EXEC master..xp_regread 
  @rootkey='HKEY_LOCAL_MACHINE',
  @key=@RegKey,
  @value_name='BackupDirectory',
  @value=@BackupDirectory OUTPUT

END

SELECT  SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [Machine Name]
    ,SERVERPROPERTY('ServerName') AS [SQL Server Name]
    ,SERVERPROPERTY('InstanceName') AS [Instance Name]
    ,SERVERPROPERTY('Collation') AS [Server Collation]

    ,'Microsoft SQL Server ' + CAST(SERVERPROPERTY('Edition') AS varchar(250)) AS Edition

    ,SERVERPROPERTY('ProductLevel') AS [Product Level]

    ,(SELECT Value FROM #ServerDetails WHERE Name = N'Language') AS [Language]

    ,(SELECT Value FROM #ServerDetails WHERE Name = N'Platform') AS [Platform]

    ,(SELECT 'Microsoft Windows NT ' + Value from #ServerDetails where Name = N'WindowsVersion') AS [Operating System]

    ,(SELECT Internal_Value FROM #ServerDetails WHERE Name = N'ProcessorCount') AS [Processors]

    ,(SELECT CAST(Internal_Value AS varchar)+ ' (MB)' FROM #ServerDetails WHERE Name = N'PhysicalMemory') AS Memory

    , CASE WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'True' ELSE 'False' END AS IsClustered

    ,(SELECT value from sys.configurations where name='min server memory (MB)') AS 'Min Server Memory (MB)'

    ,(SELECT value from sys.configurations where name='max server memory (MB)') AS 'Max Server Memory (MB)'

    ,(SELECT CASE WHEN value=0 THEN 'True' ELSE 'False' END from sys.configurations where name='affinity mask') AS 'Automatically set processor affinity mask for all processor'

    ,(SELECT CASE WHEN value=0 THEN 'True' ELSE 'False' END from sys.configurations where name='affinity I/O mask') AS 'Automatically set I/O affinity mask for all processor'

    ,CASE WHEN SERVERPROPERTY('IsIntegratedSecurityOnly')= 1 THEN 'Windows Authentication Mode' 

    WHEN SERVERPROPERTY('IsIntegratedSecurityOnly')= 0 THEN 'SQL Server and Windows Authentication Mode' END AS [Server Authentication]
    ,CASE WHEN @AuditLevel = 0 THEN 'None' 
    WHEN @AuditLevel = 1 THEN 'Successful Logins Only'
    WHEN @AuditLevel = 2 THEN 'Failed Logins Only'
    WHEN @AuditLevel = 3 THEN 'Both Failed and Successful Logins'

    END AS [Audit Level]

    ,(select CASE WHEN value = 0 THEN 'False' WHEN value = 1 THEN 'True' END from sys.configurations where name='remote access') AS 'Allow remote connections to this Server'

    ,(select CASE WHEN value = 0 THEN 'unlimited' ELSE value END from sys.configurations where name='user connections') AS 'Max number of concurrent Connections'

    ,(select CASE WHEN value = 0 THEN 'No Timeout' ELSE value END from sys.configurations where name='remote query timeout (s)') AS 'Query Timeout (s)'

    ,(select CASE WHEN value = 0 THEN 'False' WHEN value = 1 THEN 'True' END from sys.configurations where name='remote access') AS 'Allow Remote Connections to this server'

    ,@DataDirectory AS 'Default Data Directory'
    ,@LogDirectory AS 'Default Log Directory'
    ,@BackupDirectory AS 'Default Backup Directory'

    ,(SELECT value from sys.configurations WHERE name='max degree of parallelism') AS 'Max Degree of Parallelism'

    ,(SELECT value from sys.configurations WHERE name='remote login timeout (s)') AS 'Remote Login Timeout (s)'

    ,(SELECT CASE WHEN value = 0 THEN 'False' WHEN value = 1 THEN 'True' END from sys.configurations WHERE name='scan for startup procs') AS 'Scan for Startup Procs'

DROP TABLE #ServerDetails

Enjoy!! That's all in this article.