Skip to content

Instantly share code, notes, and snippets.

@Ioan-Popovici

Ioan-Popovici/SQ-SQL_Server_Products.sql

Last active July 14, 2023 10:33
Show Gist options
  • Save Ioan-Popovici/895acfbd67931422b923621d1351abc2 to your computer and use it in GitHub Desktop.
Save Ioan-Popovici/895acfbd67931422b923621d1351abc2 to your computer and use it in GitHub Desktop.
Download ZIP
Gets SQL Service Pack and Cumulative Update version information. Requires SCCM HWI Extension
Raw
SQ-SQL_Server_Products.sql
/*
.SYNOPSIS
Gets SQL product info.
.DESCRIPTION
Gets SQL product info, id and product key.
.NOTES
Created by Ioan Popovici.
Requires the usp_PivotWithDynamicColumns stored procedure (SQL Support Functions).
Requires SQL Property and ProductID HWI extensions.
Part of a report should not be run separately.
.LINK
https://MEM.Zone/SW-SQL-Server-Products
.LINK
https://MEM.Zone/SQL-SupportFunctions
.LINK
https://MEM.Zone/SW-SQL-Server-Products-CHANGELOG
.LINK
https://MEM.Zone/SW-SQL-Server-Products-GIT
.LINK
https://MEM.Zone/ISSUES
*/
/*##=============================================*/
/*## QUERY BODY */
/*##=============================================*/
/* #region QueryBody */
/* Test variable declaration !! Need to be commented for Production !! */
-- DECLARE @UserSIDs AS NVARCHAR(10) = 'Disabled';
-- DECLARE @CollectionID AS NVARCHAR(10) = 'SMS00001';
-- DECLARE @Filter AS NVARCHAR(20) = 'WID';
/* Variable declaration */
DECLARE @TableName AS NVARCHAR(MAX);
DECLARE @NonPivotedColumn AS NVARCHAR(MAX);
DECLARE @DynamicColumn AS NVARCHAR(MAX);
DECLARE @AggregationColumn AS NVARCHAR(MAX);
DECLARE @StaticColumnList AS NVARCHAR(MAX);
/* Perform cleanup */
IF OBJECT_ID('tempdb..#SQLProducts', 'U') IS NOT NULL
DROP TABLE #SQLProducts;
/* Create SQLProducts table */
CREATE TABLE #SQLProducts (
ResourceID NVARCHAR(25)
, SKUName NVARCHAR(100)
, [Version] NVARCHAR(25)
, FileVersion NVARCHAR(50)
, SPLevel NVARCHAR(2)
, IsClustered NVARCHAR(3)
, SQMReporting NVARCHAR(3)
)
/* Create SQLRelease table */
DECLARE @SQLRelease Table (FileVersion NVARCHAR(4), Release NVARCHAR(10))
/* Populate StaticColumnList */
SET @StaticColumnList = N'[SKUNAME],[VERSION],[FILEVERSION],[SPLEVEL],[CLUSTERED],[SQMREPORTING]'
/* Populate SQLRelease table */
INSERT INTO @SQLRelease (FileVersion, Release)
VALUES
('2022', '2022')
, ('2019', '2019')
, ('2017', '2017')
, ('2016', '2017')
, ('2015', '2016')
, ('2014', '2014')
, ('2013', '2014')
, ('2012', '2012')
, ('2011', '2012')
, ('2010', '2012')
, ('2009', '2008 R2')
, ('2007', '2008')
, ('2005', '2005')
, ('2000', '2000')
, ('', 'Unknown')
/* Get SQL 2022 data */
INSERT INTO #SQLProducts
EXECUTE dbo.usp_PivotWithDynamicColumns
@TableName = N'dbo.v_GS_EXT_SQL_2022_Property0'
, @NonPivotedColumn = N'ResourceID'
, @DynamicColumn = N'PropertyName0'
, @AggregationColumn = N'ISNULL(PropertyStrValue0, PropertyNumValue0)'
, @StaticColumnList = @StaticColumnList;
/* Get SQL 2019 data */
INSERT INTO #SQLProducts
EXECUTE dbo.usp_PivotWithDynamicColumns
@TableName = N'dbo.v_GS_EXT_SQL_2019_Property0'
, @NonPivotedColumn = N'ResourceID'
, @DynamicColumn = N'PropertyName0'
, @AggregationColumn = N'ISNULL(PropertyStrValue0, PropertyNumValue0)'
, @StaticColumnList = @StaticColumnList;
/* Get SQL 2017 data */
INSERT INTO #SQLProducts
EXECUTE dbo.usp_PivotWithDynamicColumns
@TableName = N'dbo.v_GS_EXT_SQL_2017_Property0'
, @NonPivotedColumn = N'ResourceID'
, @DynamicColumn = N'PropertyName0'
, @AggregationColumn = N'ISNULL(PropertyStrValue0, PropertyNumValue0)'
, @StaticColumnList = @StaticColumnList;
/* Get SQL 2016 data */
INSERT INTO #SQLProducts
EXECUTE dbo.usp_PivotWithDynamicColumns
@TableName = N'dbo.v_GS_EXT_SQL_2016_Property0'
, @NonPivotedColumn = N'ResourceID'
, @DynamicColumn = N'PropertyName0'
, @AggregationColumn = N'ISNULL(PropertyStrValue0, PropertyNumValue0)'
, @StaticColumnList = @StaticColumnList;
/* Get SQL 2014 data data */
INSERT INTO #SQLProducts
EXECUTE dbo.usp_PivotWithDynamicColumns
@TableName = N'dbo.v_GS_EXT_SQL_2014_Property0'
, @NonPivotedColumn = N'ResourceID'
, @DynamicColumn = N'PropertyName0'
, @AggregationColumn = N'ISNULL(PropertyStrValue0, PropertyNumValue0)'
, @StaticColumnList = @StaticColumnList;
/* Get SQL 2012 data */
INSERT INTO #SQLProducts
EXECUTE dbo.usp_PivotWithDynamicColumns
@TableName = N'dbo.v_GS_EXT_SQL_2012_Property0'
, @NonPivotedColumn = N'ResourceID'
, @DynamicColumn = N'PropertyName0'
, @AggregationColumn = N'ISNULL(PropertyStrValue0, PropertyNumValue0)'
, @StaticColumnList = @StaticColumnList;
/* Get SQL 2008 data */
INSERT INTO #SQLProducts
EXECUTE dbo.usp_PivotWithDynamicColumns
@TableName = N'dbo.v_GS_EXT_SQL_2008_Property0'
, @NonPivotedColumn = N'ResourceID'
, @DynamicColumn = N'PropertyName0'
, @AggregationColumn = N'ISNULL(PropertyStrValue0, PropertyNumValue0)'
, @StaticColumnList = @StaticColumnList;
/* Get SQL Legacy data */
INSERT INTO #SQLProducts
EXECUTE dbo.usp_PivotWithDynamicColumns
@TableName = N'dbo.v_GS_EXT_SQL_Legacy_Property0'
, @NonPivotedColumn = N'ResourceID'
, @DynamicColumn = N'PropertyName0'
, @AggregationColumn = N'ISNULL(PropertyStrValue0, PropertyNumValue0)'
, @StaticColumnList = @StaticColumnList;
/* Aggregate result data */
WITH SQLProducts_CTE (Release, EditionGroup, [Edition], [Version], ServicePack, CUVersion, IsClustered, Bitness, CEIPReporting, ProductKey, Device, DomainOrWorkgroup, OperatingSystem, IsVirtualMachine, CPUs, PhysicalCores, LogicalCores)
AS (
SELECT
Release = (
'SQL ' + (SELECT Release FROM @SQLRelease WHERE FileVersion = LEFT(SQLProducts.FileVersion, 4))
)
, EditionGroup = (
CASE
WHEN SQLProducts.SKUName LIKE '%enter%' THEN 'Enterprise'
WHEN SQLProducts.SKUName LIKE '%stand%' THEN 'Standard'
WHEN SQLProducts.SKUName LIKE '%expre%' THEN 'Express'
WHEN SQLProducts.SKUName LIKE '%devel%' THEN 'Developer'
WHEN SQLProducts.SKUName LIKE '%windo%' THEN 'WID'
WHEN SQLProducts.SKUName IS NULL THEN 'N/A'
ELSE 'Legacy'
END
)
, [Edition] = ISNULL(NULLIF(SQLProducts.SKUName, ''), 'N/A')
, [Version] = SQLProducts.[Version]
, ServicePack = SQLProducts.SPLevel
, CUVersion = SQLProducts.FileVersion
, IsClustered = (
CASE SQLProducts.IsClustered
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE NULL
END
)
, Bitness = (
CASE
WHEN SQLProducts.SKUName LIKE '%64%' THEN 'x64'
WHEN SQLProducts.SKUName IS NOT NULL THEN 'x86'
ELSE 'N/A'
END
)
, CEIPReporting = (
CASE SQLProducts.SQMReporting
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE NULL
END
)
, ProductKey = ISNULL(SQLProductID.DigitalProductID0, 'N/A')
, Device = Devices.[Name]
, DomainOrWorkgroup = ISNULL(Systems.Full_Domain_Name0, Systems.Resource_Domain_Or_Workgr0)
, OperatingSystem = (
IIF(
OperatingSystem.Caption0 != N''
, CONCAT(
REPLACE(OperatingSystem.Caption0, N'Microsoft ', N''), --Remove 'Microsoft ' from OperatingSystem
REPLACE(OperatingSystem.CSDVersion0, N'Service Pack ', N' SP') --Replace 'Service Pack ' with ' SP' in OperatingSystem
)
, Systems.Operating_System_Name_And0
)
)
, IsVirtualMachine = (
CASE Devices.IsVirtualMachine
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE NULL
END
)
, CPUs = COUNT(Processor.ResourceID)
, PhysicalCores = SUM(Processor.NumberOfCores0)
, LogicalCores = SUM(Processor.NumberOfLogicalProcessors0)
FROM fn_rbac_FullCollectionMembership(@UserSIDs) AS CollectionMembers
JOIN v_R_System AS Systems ON Systems.ResourceID = CollectionMembers.ResourceID
JOIN v_CombinedDeviceResources AS Devices ON Devices.MachineID = CollectionMembers.ResourceID
JOIN v_GS_PROCESSOR AS Processor ON Processor.ResourceID = CollectionMembers.ResourceID
JOIN #SQLProducts AS SQLProducts ON SQLProducts.ResourceID = CollectionMembers.ResourceID
LEFT JOIN fn_rbac_GS_OPERATING_SYSTEM(@UserSIDs) AS OperatingSystem ON OperatingSystem.ResourceID = CollectionMembers.ResourceID
LEFT JOIN dbo.v_GS_EXT_SQL_PRODUCTID0 AS SQLProductID ON SQLProductID.ResourceID = SQLProducts.ResourceID
AND SQLProductID.Release0 = (
SELECT Release FROM @SQLRelease WHERE FileVersion = LEFT(SQLProducts.FileVersion, 4)
)
AND SQLProductID.ProductID0 IS NOT NULL
WHERE CollectionMembers.CollectionID = @CollectionID
GROUP BY
SQLProducts.FileVersion
, SQLProducts.SKUName
, SQLProducts.[Version]
, SQLProducts.SPLevel
, SQLProducts.IsClustered
, SQLProducts.SQMReporting
, SQLProductID.DigitalProductID0
, Devices.[Name]
, Systems.Full_Domain_Name0
, Systems.Resource_Domain_Or_Workgr0
, Systems.Operating_System_Name_and0
, Systems.Build01
, OperatingSystem.Caption0
, OperatingSystem.CSDVersion0
, Devices.IsVirtualMachine
, Processor.NumberOfCores0
, Processor.NumberOfLogicalProcessors0
)
/* Filter results */
SELECT
Release
, EditionGroup
, [Edition]
, [Version]
, ServicePack
, CUVersion
, IsClustered
, Bitness
, CEIPReporting
, ProductKey
, Device
, DomainOrWorkgroup
, OperatingSystem
, IsVirtualMachine
, CPUs
, PhysicalCores
, LogicalCores
FROM SQLProducts_CTE
WHERE EditionGroup NOT IN (@Filter)
/* Perform cleanup */
IF OBJECT_ID('tempdb..#SQLProducts', 'U') IS NOT NULL
DROP TABLE #SQLProducts;
/* #endregion */
/*##=============================================*/
/*## END QUERY BODY */
/*##=============================================*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment