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