Below script is used to find the detailed information about the configuration of SQL Server
create table #SVer(ID int, Name sysname, Internal_Value int, Value nvarchar(512))
insert #SVer exec master.dbo.xp_msver
declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT
declare @HkeyLocal nvarchar(18)
declare @ServicesRegPath nvarchar(34)
declare @SqlServiceRegPath sysname
declare @BrowserServiceRegPath sysname
declare @MSSqlServerRegPath nvarchar(31)
declare @InstanceNamesRegPath nvarchar(59)
declare @InstanceRegPath sysname
declare @SetupRegPath sysname
declare @NpRegPath sysname
declare @TcpRegPath sysname
declare @RegPathParams sysname
declare @FilestreamRegPath sysname
select @HkeyLocal=N'HKEY_LOCAL_MACHINE'
-- Instance-based paths
select @MSSqlServerRegPath=N'SOFTWARE\Microsoft\MSSQLServer'
select @InstanceRegPath=@MSSqlServerRegPath + N'\MSSQLServer'
select @FilestreamRegPath=@InstanceRegPath + N'\Filestream'
select @SetupRegPath=@MSSqlServerRegPath + N'\Setup'
select @RegPathParams=@InstanceRegPath+'\Parameters'
-- Services
select @ServicesRegPath=N'SYSTEM\CurrentControlSet\Services'
select @SqlServiceRegPath=@ServicesRegPath + N'\MSSQLSERVER'
select @BrowserServiceRegPath=@ServicesRegPath + N'\SQLBrowser'
-- InstanceId setting
select @InstanceNamesRegPath=N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
declare @InstallSqlDataDir nvarchar(512)
exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLDataRoot', @InstallSqlDataDir OUTPUT
declare @BackupDirectory nvarchar(512)
exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'BackupDirectory', @BackupDirectory OUTPUT
create table #serverinfo (Name varchar(100), Value sql_variant)
insert #serverinfo
select 'Machine Name' Name,serverproperty('Machinename') Value
union all
select 'Server Name',@@Servername
union all
select name,value
from #SVer
where name in ('ProductVersion','Language','PhysicalMemory','ProcessorCount','FileDescription')
union all
select 'RootDirectory',@SmoRoot
union all
select name,value_in_use
from sys.configurations
where
name in ('max server memory (MB)','max degree of parallelism','cost threshold for parallelism',
'default trace enabled','Database Mail XPs')
union all
select 'Edition',serverproperty('Edition')
union all
select 'Product Level',serverproperty('productlevel')
union all
select 'Version',replace(substring(@@version,1,charindex('-', (@@version))),'-','')
union all
select 'collation',serverproperty('collation')
union all
select 'IsCaseSensitive',CAST(case when 'a' <> 'A' then 1 else 0 end AS bit)
union all
select 'ResourceVersionString',SERVERPROPERTY(N'ResourceVersion')
union all
select 'ResourceLastUpdateDateTime',SERVERPROPERTY(N'ResourceLastUpdateDateTime')
union all
select 'IsClustered',serverproperty('isclustered')
union all
select 'IsIntegratedSecurityOnly',serverproperty('IsIntegratedSecurityOnly')
union all
select 'IsSingleUser',serverproperty('IsSingleUser')
union all
select 'SQLDataRoot', @InstallSqlDataDir
union all
select 'BackupDirectory', @BackupDirectory
union all
select name,enabled from msdb.dbo.sysjobs
union all
select name,'EMail ID:- '+email_address+' Net Send :-'+isnull(netsend_address,' ') from msdb.dbo.sysoperators
union all
select name,has_notification from msdb.dbo.sysalerts
select * from #serverinfo
Drop table #SVer,#serverinfo
