Useful information about the SQL instance

This script gets useful information about the SQL instance, like:
- Name
- SQL Version
- Time Zone
- Max Degree of Parallelism (Configured , Running & Optimal)
- Number of Cores
- Memory
- Cluster ot Standalone
- SQL Server service start time

-- MAIN SCRIPT

--// SQL Version

SET NOCOUNT ON

DECLARE

@date datetime,

@start int,

@ver varchar(13),

@config_value varchar(20),

@run_value varchar(20),

@sqlstart datetime,

@Mem int,

@optimal_maxdop int,

@cpu_count int,

@hyperthread_ratio int

DECLARE @CM Table

(

[Index] int,

Name nvarchar(1000) NOT NULL,

Internal_Value int,

Character_Value nvarchar(1000)

)

SELECT @date = getdate()

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2005',@@version)

if @start = 1

SELECT @ver = rtrim(substring(@@version,29,13))

if @start = 0

SELECT @ver = rtrim(substring(@@version,30,9))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2008',@@version)

if @start = 1

SELECT @ver = rtrim(substring(@@version,35,12))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2008 R2',@@version)

if @start = 1

SELECT @ver = rtrim(substring(@@version,38,12))

-- MAX DEGREE OF PARALLELISM

CREATE TABLE #MDP

(

name nvarchar(1000),

minimun int NOT NULL,

maximun int NOT NULL,

config_value int NOT NULL,

run_value int NOT NULL

)

Insert into #MDP exec sp_configure 'max degree of parallelism'

SELECT @config_value=rtrim(convert(varchar(8),config_value)) ,@run_value=rtrim(convert(varchar(8),run_value)) from #MDP

DROP TABLE #MDP

select @sqlstart = create_date from sys.databases where name = 'Tempdb'

-- CPU and Memory

Insert into @CM exec xp_msver

--//REPORT

select @Mem = Internal_Value from @CM Where Name = 'PhysicalMemory'

select

@cpu_count=cpu_count,

@hyperthread_ratio=hyperthread_ratio,@optimal_maxdop=case

when cpu_count / hyperthread_ratio > 8 then 4

else CEILING((cpu_count / hyperthread_ratio)*.5)

end

from sys.dm_os_sys_info;

--//REPORT

SELECT

@@servername as Servername

,getdate() as [Current Date/Time]

,DATEDIFF(HH,GETUTCDATE(),GETDATE()) as [Time Zone]

,@ver as SQL_version

,@config_value as [MDP cfg]

,@run_value as [MDP run]

,@hyperthread_ratio as [Hyperthread Ratio]

,@optimal_maxdop as [Optimal MDP]

,@sqlstart as sql_srv_start

,serverproperty('Edition') as sql_edition

,case serverproperty('IsClustered') when 0 THEN 'NO' when 1 THEN 'YES' end as IsCluster

,@cpu_count as [CPU Count]

,@Mem as [Memory (MB)]