Friday 9 November 2012

SQL Server Detailed Configuration Information

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