Determining the version of SQL Server installed components by PowerShell

In chapter 1, talk about how to determine which version and edition of SQL Server Database Engine is running by SQL Script. It also lists the latest Cumulative Update and Service Pack. The Current SQL Server can be upgraded to which editions.


In SQL Server, can only use master.dbo.xp_regread procedure reading registries to get components version information, some enterprises in order to safely remove the procedure. So, in this chapter, we will use PowerShell to determine the version of SQL Server installed components.


You can use this script in the following way.

Step 1: Ensure that SQL Server (MSSQLSERVER) service is running

Step 2: Run Windows PowerShell as administrator.

Setp 3: Execution Set-ExecutionPolicy unrestricted -Force

Step 4: Then input the path of DetermineVersionOfComponents.ps1 and press Enter.

Step 5: Sequentially input SQL Server instance name, user name, password.


As shown below, it will return the version number of the components. If the version number is empty, indicating the component is not installed.


Here are some code snippets for your reference.


[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo"| Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo"| Out-Null  
$ServerConnection =new-object "Microsoft.SqlServer.Management.Common.ServerConnection" $ServerInstance,$UserName,$Password 
$Server=New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerConnection


More information:

For more information about how to determine the versions and edition of SQL Server and its components, we recommend going to Microsoft Knowledge Base article 321185.

How to Install Windows PowerShell 4.0


Windows PowerShell 3.0 or higher

SQL Server 2008 or higher