SQL Server details using T-SQL

You can get SQL Server details by executing 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