SQL Server - How to identify version and edit of all server instances using xp_regread and Powershell

Views: 225
Reading Time: 9 minutes

Hello everybody!

In this post, I would like to share with you some ways to identify the version and edition of all server instances using xp_regread and Powershell. I saw a user submitting this question in a Whatsapp group and didn't find many solutions on the internet, which motivated me to write this article.

How to identify the version and edition of the logged in instance

If you want to identify the information of the instance to which you are connected, it is quite simple:

Result:

You can also identify this information using the SERVERPROPERTY function:

Result:

Identifying the version and edition of all instances

The problem at hand is when you need to identify information from servers that have multiple instances, which can end up making this activity a little labor intensive, depending on the number of server instances.

How to identify the version and edition of all instances using T-SQL

To meet this need, I wrote the script below, which uses the system procedure xp_regread to search for instance names in the Windows registry. After that, retrieves the information from each of these instances and still uses the procedure xp_cmdshell to read information from the ERRORLOG file of each instance to identify some information (practically simulates @@ VERSION).

View source

Result:

How to identify the version and edition of all instances using Powershell

Another way to be able to capture basic information from all instances of a server list is by using the Powershell script below:

View source (using PowerShell only)
View source code (using T-SQL, xp_cmdshell, and PowerShell)

In this example, you will populate the input file (C: \ Temp \ servers.txt) with the hostname of the servers you want to parse SQL Server editions and versions of.

After running the Powershell script, a CSV file (C: \ Temp \ version.csv) will be generated with the collected information:

That's it folks!
I hope you enjoyed this post and see you next time.