Getting SQL version info from list of server names RRS feed

  • Question

  • I am wondering if there is a powershell script I can run against a .txt file with server names in it that will return the version of SQL on that list of servers. Any help is appreciated.


    Chad Guiney

    Friday, July 5, 2019 2:33 PM

All replies

  • Thanks! this page has what I am looking for but looking at the example it wants one computer name at a time. I ran get-sqlsvrver -ComputerName one_of_my_SQL_servnames

    But I need to know how I can get it to read a list of server names from a text file and then get the results. The script it below. Can someone tell me how to use this script and have it read a .txt file with my server names in it?

    Function Get-SQLSvrVer {
            Checks remote registry for SQL Server Edition and Version.

            Checks remote registry for SQL Server Edition and Version.

        .PARAMETER  ComputerName
            The remote computer your boss is asking about.

            PS C:\> Get-SQLSvrVer -ComputerName mymssqlsvr 

            PS C:\> $list = cat .\sqlsvrs.txt
            PS C:\> $list | % { Get-SQLSvrVer $_ | select ServerName,Edition }



            Only sissies need notes...


        # a computer name
        [Parameter(Position=0, Mandatory=$true)]

    # Test to see if the remote is up
    if (Test-Connection -ComputerName $ComputerName -Count 1 -Quiet) {
        # create an empty psobject (hashtable)
        $SqlVer = New-Object PSObject
        # add the remote server name to the psobj
        $SqlVer | Add-Member -MemberType NoteProperty -Name ServerName -Value $ComputerName
        # set key path for reg data
        $key = "SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"
        # i have no idea what this does, honestly, i stole it...
        $type = [Microsoft.Win32.RegistryHive]::LocalMachine
        # set up a .net call, uses the .net thingy above as a reference, could have just put 
        # 'LocalMachine' here instead of the $type var (but this looks fancier :D )
        $regKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $ComputerName)

        # make the call 
        $SqlKey = $regKey.OpenSubKey($key)
            # parse each value in the reg_multi InstalledInstances 
            Foreach($instance in $SqlKey.GetValueNames()){
            $instName = $SqlKey.GetValue("$instance") # read the instance name
            $instKey = $regKey.OpenSubkey("SOFTWARE\Microsoft\Microsoft SQL Server\$instName\Setup") # sub in instance name
            # add stuff to the psobj
            $SqlVer | Add-Member -MemberType NoteProperty -Name Edition -Value $instKey.GetValue("Edition") -Force # read Ed value
            $SqlVer | Add-Member -MemberType NoteProperty -Name Version -Value $instKey.GetValue("Version") -Force # read Ver value
            # return an object, useful for many things
    } else { Write-Host "Server $ComputerName unavailable..." } # if the connection test fails

    Chad Guiney

    Friday, July 5, 2019 4:26 PM