Snippet Name: envversion
Tags: helpful,scriptgeneration
Created Date: Oct 1 2021 8:05AM
Last Modified Date: Apr 1 2022 11:22AM
–desc: deep details about the server and it’s version
Dependencies: none-standalone code
This snippet produces a single roles with eight Honda of information about this specific server as far as windows SQL server settings and a lot of information it is very dense and it has it’s the biggest advantage when you’ve collected for all your servers in a big collection.
Sample Output:
--desc: deep details about the server and it's version
IF OBJECT_ID('tempdb..[#t]') IS NOT NULL
DROP TABLE [#t]
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @version NVARCHAR(32)
DECLARE @edition INT
DECLARE @SerialNumber VARCHAR(30);
DECLARE @cpu_count int;
DECLARE @hyperthreadratio int;
DECLARE @physicialCPUSockets int;
DECLARE @PhysicalMemoryMB int;
DECLARE @VirtualMachine varchar(50)
DECLARE @ServerRestartedDate varchar(30)
DECLARE @SQLMemory INT;
DECLARE @Domain VARCHAR(128);
DECLARE @key VARCHAR(128)
DECLARE @FullServerName VARCHAR(128);
DECLARE @IPAddress VARCHAR(128);
DECLARE @TCPPort VARCHAR(128);
DECLARE @WindowsVersion VARCHAR(128);
SELECT @ServerRestartedDate = CONVERT(varchar(30),dbz.create_date,120) from sys.databases dbz where name='tempdb'
SELECT @SQLMemory = CONVERT(int,value) FROM sys.[configurations] WHERE name = 'max server memory (MB)'
SET @key = 'SYSTEM\ControlSet001\Services\Tcpip\Parameters\'
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@key,
@value_name='Domain',
@value=@Domain OUTPUT
SELECT @Domain = '.' + @Domain
SELECT
@FullServerName = fn.FullServerName,
@IPAddress = IPAddress,
@TCPPort = TCPPort
FROM (
SELECT TOP 1 local_net_address AS IPAddress,
CONVERT(VARCHAR(30), local_tcp_port) AS TCPPort,
local_net_address + ' | ' + CONVERT(VARCHAR(30), local_tcp_port) AS IpAndPort
FROM sys.dm_exec_connections
WHERE local_net_address is not NULL
AND [protocol_type] = 'TSQL'
AND [local_net_address] <> '127.0.0.1'
) x
CROSS APPLY(SELECT CONVERT(VARCHAR(128),@@SERVERNAME) As ServerName) prefn1
CROSS APPLY(SELECT prefn1.ServerName,CHARINDEX('\',prefn1.ServerName) AS chrServer ) prefn2
CROSS APPLY(SELECT prefn2.ServerName,
CASE
WHEN prefn2.chrServer > 0
THEN SUBSTRING(prefn1.ServerName,1,prefn2.chrServer -1) + @Domain + SUBSTRING(prefn1.ServerName,prefn2.chrServer,128)
ELSE prefn1.ServerName + '.' + @Domain
END AS FullServerName
) fn
IF OBJECT_ID('tempdb.[dbo].[#CPUData]') IS NOT NULL
DROP TABLE [dbo].[#CPUData]
CREATE TABLE #CPUData (cpu_count int, hyperthread_ratio int,physicialCPUSockets int,PhysicalMemoryMB int,VirtualMachine varchar(50),VirtualMachineType varchar(50))
--SQL2014 and above, column is different
--select Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4) + '.' + Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
IF CONVERT(DECIMAL(5,2),PARSENAME(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)
+ '.'
+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3))
>=11.0
BEGIN
INSERT INTO #CPUData([cpu_count],[hyperthread_ratio],[physicialCPUSockets],[PhysicalMemoryMB],[VirtualMachine],[VirtualMachineType])
EXEC(
'SELECT
s.cpu_count ,
s.hyperthread_ratio ,
s.cpu_count / s.hyperthread_ratio As NumberofCores,
s.physical_memory_kb/1024 As MemoryinMb,
s.virtual_machine_type_desc,
CASE WHEN s.virtual_machine_type > 0 THEN ''Virtual'' ELSE '''' END As IsVirtual--1 = hypervisor, 2 = other type of virtual
FROM sys.dm_os_sys_info s');
END
ELSE
BEGIN
INSERT INTO #CPUData([cpu_count],[hyperthread_ratio],[physicialCPUSockets],[PhysicalMemoryMB],[VirtualMachine],[VirtualMachineType])
EXEC(
'SELECT
s.cpu_count ,
s.hyperthread_ratio ,
s.cpu_count / s.hyperthread_ratio As NumberofCores,
s.physical_memory_in_bytes/1048576 As MemoryinMb,
'''' AS virtual_machine_type_desc,
'''' As IsVirtual
FROM sys.dm_os_sys_info s');
END
IF EXISTS (SELECT * FROM sys.all_columns WHERE object_name(object_id) ='dm_os_sys_info' AND name = 'virtual_machine_type')
BEGIN
EXEC(
'UPDATE MyTarget
SET VirtualMachine = CASE WHEN s.virtual_machine_type > 0 THEN ''Virtual'' ELSE '''' END
--SELECT CASE WHEN s.virtual_machine_type > 0 THEN ''Virtual'' ELSE '''' END --1 = hypervisor, 2 = other type of virtual
FROM sys.dm_os_sys_info s
CROSS JOIN #CPUData MyTarget');
END
SELECT --@@SERVERNAME
@cpu_count = s.cpu_count ,
@hyperthreadratio = s.hyperthread_ratio ,
@physicialCPUSockets = s.physicialCPUSockets ,
@PhysicalMemoryMB = s.PhysicalMemoryMB,
@VirtualMachine = s.VirtualMachine
FROM #CPUData s ;
SELECT @WindowsVersion =
CASE
WHEN CONVERT(VARCHAR(4000),@@VERSION) LIKE '%11.0% <x64>' THEN 'Windows Server 2019'
WHEN CONVERT(VARCHAR(4000),@@VERSION) LIKE '%10.0 <x64>%' THEN 'Windows Server 2016'
WHEN CONVERT(VARCHAR(4000),@@VERSION) LIKE '%6.3 <x64>%' THEN 'Windows Server 2012 R2'
WHEN CONVERT(VARCHAR(4000),@@VERSION) LIKE '%NT 6.2%' THEN 'Windows Server 2012'
WHEN CONVERT(VARCHAR(4000),@@VERSION) LIKE '%NT 6.1%' THEN 'Windows Server 2008 R2'
WHEN CONVERT(VARCHAR(4000),@@VERSION) LIKE '%NT 6.0%' THEN 'Windows Server 2008'
WHEN CONVERT(VARCHAR(4000),@@VERSION) LIKE '%NT 5.2%' THEN 'Windows Server 2003 R2'
WHEN CONVERT(VARCHAR(4000),@@VERSION) LIKE '%NT 5.2%' THEN 'Windows Server 2003'
WHEN CONVERT(VARCHAR(4000),@@VERSION) LIKE '%NT 5.0%' THEN 'Windows 2000'
WHEN CONVERT(VARCHAR(4000),@@VERSION) LIKE '%NT 4.0%' THEN 'Windows NT 4.0'
WHEN CONVERT(VARCHAR(4000),@@VERSION) LIKE '%NT 3.51%' THEN 'Windows NT 3.51'
WHEN CONVERT(VARCHAR(4000),@@VERSION) LIKE '%NT 3.5%' THEN 'Windows NT 3.5'
ELSE 'Unknown'
END
IF EXISTS(SELECT * from master.sys.objects where name = 'sqbutility' and type_desc = 'EXTENDED_STORED_PROCEDURE')
BEGIN
EXEC master..sqbutility
21,
@edition OUTPUT,
@version OUTPUT,
@SerialNumber OUTPUT;
END
ELSE
BEGIN
SELECT @version = '' ,
@edition = 0,
@SerialNumber = ''
END
SELECT @ServerRestartedDate As ServerRestartedDate,
@Domain AS DomainName,
CONVERT(VARCHAR(128),Serverproperty('ComputerNamePhysicalNetBIOS')) + @Domain AS FQComputerName,
CONVERT(VARCHAR(128),SERVERPROPERTY('MachineName')) + @Domain AS FQServerName,
@FullServerName AS FQInstanceName,
@WindowsVersion AS WindowsVersion,
REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(4000),@@Version),CHAR(13),' '),CHAR(10),' '),CHAR(9),' ') AS SQLVersionString,
@IPAddress AS IPAddress,
@TCPPort AS TCPPort,
--@@version,
CONVERT(VARCHAR(128),Serverproperty('BuildClrVersion')) AS BuildClrVersion,
CONVERT(VARCHAR(128),Serverproperty('ComputerNamePhysicalNetBIOS')) AS ComputerNamePhysicalNetBIOS,
CONVERT(VARCHAR(128),Serverproperty('Edition')) AS Edition,
CONVERT(VARCHAR(128),CASE CONVERT(DECIMAL(5,2),Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)
+ '.'
+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3))
WHEN 9.00 THEN 'SQL 2005'
WHEN 10.0 THEN 'SQL 2008'
WHEN 10.50 THEN 'SQL 2008R2'
WHEN 11.0 THEN 'SQL 2012'
WHEN 12.0 THEN 'SQL 2014'
WHEN 13.0 THEN 'SQL 2016'
WHEN 14.0 THEN 'SQL 2017'
WHEN 15.0 THEN 'SQL 2019'
ELSE Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)
+ '.'
+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
END) As SQLVersion,
CONVERT(VARCHAR(128),Serverproperty('productversion')) As ProductVersion,
CONVERT(VARCHAR(128),Serverproperty('EditionID')) AS EditionID,
CONVERT(VARCHAR(128),Serverproperty('EngineEdition')) AS EngineEdition,
CONVERT(VARCHAR(128),Serverproperty('MachineName')) AS MachineName,
CONVERT(VARCHAR(128),Serverproperty('ProductLevel')) AS ProductLevel,
CONVERT(VARCHAR(128),Serverproperty('ResourceLastUpdateDateTime')) AS ResourceLastUpdateDateTime,
CONVERT(VARCHAR(128),Serverproperty('ResourceVersion')) AS ResourceVersion,
CONVERT(VARCHAR(128),Serverproperty('ServerName')) AS ServerName,
ISNULL( CONVERT(VARCHAR(128),Serverproperty('InstanceName')),'') AS InstanceName ,
CASE
WHEN CHARINDEX('(Hypervisor)',CONVERT(varchar(8000),@@version)) > 0
THEN '(Hypervisor)'
ELSE ''
END As VM,
@VirtualMachine AS VirtualMachine,
@version AS RedGateVersion,
@edition AS RedGateEdition,
@SerialNumber AS RedGateSerialNumber,
@cpu_count AS CPUCount,
@hyperthreadratio AS HyperThreadRatio,
@physicialCPUSockets AS PhysicalCPUSockets,
@SQLMemory AS [SQLMaxMemory(MB)],
@PhysicalMemoryMB As [PhysicalMemory(MB)],
CONVERT(money,((@SQLMemory *1.0) / @PhysicalMemoryMB) * 100) AS PercentMemoryForSQL,
Getdate() AS DWCreatedDate
INTO #t
SELECT * FROM #t