Inventory SQL Server Services Version and Edition

By:   |   Comments (14)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > SQL Server Configurations


I will be working for a new company soon and on the first day I want to understand the scope of the new environment in terms of the number of installed SQL Server instances (including SSAS, SSRS etc.), their versions / editions and service accounts. I am told they have hundreds of SQL Server instances, from SQL Server 2005 to SQL Server 2014, on around 200+ boxes, how can I get all of this information?


In SQL SMO, there is a class named ManagedComputer, which can provide information about SQL Server services (like SSAS, SSIS, SSRS, and SQL Server Engine) installed on a computer. With this information, we can then take a different approach to retrieve version and edition values of each service. So here are the key points in designing the solution:

  • For the SQL Server service, we will use SMO Server class to retrieve version/edition values.
  • For the SSAS service, we will use AMO Server class to retrieve version/edition values.
  • For the SSRS service, it is tricky. For SSRS 2005, we will use the SSRS executable file version as the SSRS version number, and we will use WMI class MSReportServer_Instance to find the edition value. For SSRS 2008+, we will simply use MSReportServer_Instance to get both version and edition values.
  • For the SSIS service, it is even trickier because it seems Microsoft never officially stores SSIS edition anywhere, or in other words, it seems there is no edition property for the SSIS service. But in logic, it does not make sense because if I install a Standard SQL Server version on my computer together with SSIS, I expect this SSIS to be Standard edition (instead of Enterprise per se). So here, I will use the file version of the SSIS executable file as the SSIS version and then use registry data to find the SSIS edition data.
  • We will use PowerShell to drive the whole logic and save the results to a data table, which can be saved to a csv file or to a SQL Server table.

The PS code below is to be run on a computer with PowerShell V3+ and SQL Server 2012 Client Tools SDK installed (as the minimum requirement), and you should have access permissions to all servers you want to scan. In my case, I have local admin privileges on all the servers against which the script runs.


#Requires -version 3.0
add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement, version=, Culture=Neutral, PublicKeyToken=89845dcd8080cc91";
add-type -AssemblyName "Microsoft.AnalysisServices, version=, Culture=Neutral, PublicKeyToken=89845dcd8080cc91";

$data_table = New-Object "";
$col = New-Object "" ('MachineName', [System.String]);
$col = New-Object "" ('ServerInstance', [System.String]);
$col = New-Object "" ('Type', [System.String]); #type=SQLServer / AnalysisServer / ReprtServer / IntegrationService 
$col = New-Object "" ('Version', [System.String]);
$col = New-Object "" ('Edition', [System.String]);
$col = New-Object "" ('ServiceAccount', [System.String]);

[string[]]$server_list= 'SrvDev06', 'SvrQA26', 'SvrDev26', 'SvrPre57','SvrQA04'; #change to your own server names
# [string[]]$server_list = gc -path 'c:\temp\server_list.txt' #you can put your server list in a text file, each [ServerName] uses one line

foreach ($machine_name in $server_list)
{   "processing : $machine_name";
        $mc = new-object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" $machine_name;
        $ | ? {($_.type -in ("SqlServer", "AnalysisServer", "ReportServer", 'SqlServerIntegrationService') ) -and ($_.ServiceState -eq 'Running')} | # % {($"$")}
        % { $s = $;
            [string]$svc_acct = $_.ServiceAccount;
            switch ($_.type) 
            { "sqlserver" { if ($s.contains("$")) {$sql_instance= "$($machine_name)\$($s.split('$')[1])"} else {$sql_instance=$machine_name;} 
                                $sql_svr = new-object "" $sql_instance;
                                $row = $data_table.NewRow();
                                $row.Edition = $sql_svr.Edition; 
                                $row.Version = $sql_svr.Version;
                                $row.Type = 'SQLServer';
                                $row.ServerInstance = $sql_instance;
                                $row.ServiceAccount = $svc_acct;
                          } #sqlserver 

              "AnalysisServer"  { if ($s.contains("$")) {$as_instance= "$($machine_name)\$($s.split('$')[1])"} else {$as_instance=$machine_name;} 
                                  $as_svr = New-Object "Microsoft.AnalysisServices.Server";
                                  $as_svr.connect("data source=$as_instance");
                                  $row = $data_table.NewRow();
                                  $row.Edition = $as_svr.Edition; 
                                  $row.Version = $as_svr.Version;
                                  $row.Type = 'AnalysisServer';
                                  $row.ServerInstance = $as_instance;
                                  $row.ServiceAccount = $svc_acct;
                                } #AnalysisServer 

              "ReportServer"  {
                                $pathname = ($[$s]).PathName;
                                $pathname= "\\$machine_name\" + ($pathname.replace(':\', '$\')).replace('"', '')
                                $item=get-item $pathname
                                [string]$ver='V' + ($item.VersionInfo.ProductMajorPart).ToString();
                                [string]$file_version = $item.VersionInfo.ProductVersion;
                                if ($s.Contains('$')) # this is a named instance of SSRS

                                    [string]$instance_name = (($s.split('$'))[1]).replace('_', '_5f'); #SSRS instance name is encoded

                                    [string]$instance_name = 'MSSQLSERVER';

                                if ($ver -eq 'V9') 
                                #for sql 2005 SSRS, there is no direct version number from WMI interface, so I have to use SSRS executable file version info as SSRS version
                                    gwmi -class MSReportServer_Instance –Namespace “root\microsoft\sqlserver\reportserver\V9” -ComputerName $machine_name | 
                                    Where-Object {$_.__Path -like "*InstanceName=`"$($instance_name)`"" } | 
                                    % {   $row = $data_table.NewRow();
                                          $row.Edition = $_.EditionName; 
                                          $row.Version = $File_Version;
                                          $row.Type = 'ReportServer';
                                          $row.ServerInstance = $s;
                                          $row.ServiceAccount = $svc_acct;


                                   gwmi -class MSReportServer_Instance –Namespace “root\microsoft\sqlserver\reportserver\$rs_name\$ver” -ComputerName $machine_name | 
                                   Where-Object {$_.__Path -like "*InstanceName=`"$($instance_name)`"" } | 
                                   %  {   $row = $data_table.NewRow();
                                          $row.Edition = $_.EditionName; 
                                          $row.Version = $_.version;
                                          $row.Type = 'ReportServer';
                                          $row.ServerInstance = $s;
                                          $row.ServiceAccount = $svc_acct;
                            } #ReportServer
              'SqlServerIntegrationService' {
                                                $pathname = ($[$s]).PathName;
                                                $pathname= "\\$machine_name\" + ($pathname.replace(':\', '$\')).replace('"', '');
                                                $item=get-item $pathname;
                                                [string]$ver= ($item.VersionInfo.ProductMajorPart).ToString() +'0';
                                                [string]$file_version = $item.VersionInfo.ProductVersion;

                                                #finding the SSIS edition by reading the registry
                                                $key="SOFTWARE\MICROSOFT\Microsoft SQL Server\$ver\Tools\Setup";
                                                $type = [Microsoft.Win32.RegistryHive]::LocalMachine;
                                                $regkey=[Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $machine_name);
                                                $row = $data_table.NewRow();
                                                $row.Edition = $r; 
                                                $row.Version = $file_version;
                                                $row.Type = 'IntegrationService';
                                                $row.ServerInstance = $s;
                                                $row.ServiceAccount = $svc_acct;

                                            } #sqlserverIntegrationService 
        Write-Error $Error[0].Exception

$data_table | select machineName, serverinstance, type, version, edition | ft -auto

#if you want to export to an csv file, you can do the following, assuming you have c:\temp\ folder
$data_table | select machineName, serverinstance, type, version, edition | export-csv -path c:\temp\test.csv -notypeinfo -Force

After running the script in PS ISE, I will get the following result.  Note: all values, except for the [Type] and [Version] columns, have been modified for security reasons. Also the ServiceAccount is purposely omitted.



Completing a SQL Server inventory review is very useful for understanding the scope and complexity of your working environment. Inventory data collection can include many more data points, for example, OS version, disk drives, CPU, memory, etc. Even for the SQL Server instance itself, you may collect data points such as server configuration, databases, security (i.e. logins, credentials, certificates, etc.), replication, mirroring, log-shipping, HAGs, etc.  In this tip, I only reviewed the basic (yet arguably the most important) data points i.e. SQL Services, that a DBA needs to know before starting his/her work.

Note: this script does not apply to SQL Server 2000 services, which at this point I assume are very few and seldom used.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, September 28, 2020 - 1:59:33 PM - Jeffrey Back To Top (86542)
Hello I wanted to know if someone has an idea on why I'm seeing this error. I used this on another server and it works.

"get-item : Cannot find path '\\\E$\Program Files\Microsoft SQL Server\120\DTS\Binn\MsDtsSrvr.exe' because it does not exist.
At C:\Temp\SQLVer_List.ps1:123 char:55......"

This path does exist..

Thanks in advance

Thursday, November 3, 2016 - 1:59:34 PM - Josh Back To Top (43695)

Could not get this to work.  I am not a DB guy, only dabble in coding.  I'm a Sys Admin/Infrastructure guy tryin' t get an inventory of (amongst other things) SQL servers in my organization.  Didn't work for me.  Nice try tho.

Wednesday, May 25, 2016 - 12:07:16 PM - jeff_yao Back To Top (41558)

Hi Slogmeister

I said in the tip

"The PS code below is to be run on a computer with PowerShell V3+ and SQL Server 2012 Client Tools SDK installed (as the minimum requirement), and you should have access permissions to all servers you want to scan"

In my environment, I run the script on a dedicated DBA server box with PS V4 and SQL Server 2012 client tools sdk installed, but the sql servers I need to scan include sql server 2005/2k8/2k8R2/2012/2014.

Hope this helps/clarifies your confusion.







Wednesday, May 25, 2016 - 10:21:13 AM - Slogmeister Extraordinaire Back To Top (41557)
If you know to use this line: add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement, version=, Culture=Neutral, PublicKeyToken=89845dcd8080cc91"; You already know the version of SQL. Your line only works with SQL 2012, it doesn't work with SQL 2008. Not terribly useful.

Monday, April 13, 2015 - 1:21:03 PM - jeff_yao Back To Top (36918)

Hi Steven,

I have not tested how to get required info without sysadmin privilege. I guess you can try to create an account on your own computer without giving the account sysadmin privilege and then just test it by incremently granting necessary privileges until the privilege is just good enough to get the info. If you really take this approach, I recommend you to use Profiler (with some strong filters) to check each step, and it will be much easier.




Monday, April 13, 2015 - 9:32:18 AM - Steven Back To Top (36915)

Hi Jeffrey,

I have a simular script to get SQL server information. My problem is that I'm not allowed to use a locak admin account or SQL sysadmin. The account I use has remote WMI permissions and datareader access to MSDB and Master database. Additionaly it also has view server state.

I get all the info I need except Service Pack info and Edition. 


$SQLSMOinfo = New-Object Microsoft.SqlServer.Management.Smo.Server $computer



Do you have any idea how I can get this information without sysadmin rights?





Tuesday, March 17, 2015 - 1:06:56 PM - jeff_yao Back To Top (36563)

@Daisy, not sure why you encounter the issue. Are you using cluster name directly (which will not work) or using the cluster node machine name (which should work)?

Also have you tried run the script against a non-clustered server box and do you still see the same issue?

Tuesday, March 17, 2015 - 8:40:00 AM - Jeff Bennett Back To Top (36558)

Jeffrey Yao,

If you are getting information stating "Not Supported", perhaps you are connecting to older versions of SQL 2000 and previous editions, or MSDE instead of SQL Express.  If that is the case, you will have to research those individual servers on a case-by-case basis, because the MAP tool version is too recent, and while it can detect the SQL instance for older servers, it can not gather a lot of the infrastructure information on those legacy servers.

I have never had problems running the tool against multiple instances on a server.  What version of MAP are you running?

Regarding the preference for '2012SP1' or '11.0.3393.0', it is probably in the tables somewhere, but you will have to search for that, and may be better off just using a case statement against the specific tables.  Some of those tables within the MAP database are easy to find, but difficult to decipher.  Try these schemas




WinServer_Assessment schema



Monday, March 16, 2015 - 3:56:23 PM - Daisy Back To Top (36547)

Hi, Jeffrey, 

I tried to run the script to retrieve the info from a clustered sql server with multiple instances, how does this works? (for sql 2008 r2 and sql 2012). 

Also I tried some of other properties, like ProductLevel or OSVersion, keep saying not supported. So what is the right server properties if I want to restrieve something like this 2012SP1 instead of 11.0.3393.0. thanks


Wednesday, March 11, 2015 - 6:10:47 PM - jeff_yao Back To Top (36501)

@JeffBennett, thanks for your comments.

I will try MPA tool some time later. 

The code I presented here is actually part of my "inventory collection" project. It is nimble in the sense that you can almost run it from a PS ISE. I have also created a few PS cmdlet based on the similar code that I can use in various scenarios, one example is that after our server team patches OS on a few servers, and then I need to check whether my sql services on these servers start as expected, all I need to run is such a cmdlet:

get-SQLServiceInfo -Computer 'Server1', 'Server2', 'ServerN' 

What I mean is that understanding how to collect sql service info conveniently may save us quite some time in daily work.

I am personally always interested in figuring out how something is done even though it had been done by someone before. :-)

But I agree, if we want to deal with MS licensing issue, we'd better let MS tool to tell us the details.


Kind regards,

Jeff Yao

Wednesday, March 11, 2015 - 4:27:05 PM - Jeff Bennett Back To Top (36500)

When I took over as team lead at my company, I found about 80 instances that were not known by my manager or team members, simply by running the Microsoft Analysis and Planning Toolkit.    Iwould not ever want to have to use the excuse that 'since I didn't know about it, I don't have to manage it' in response to a problematic SQL server in my corporate domain.   I would never expect my management to know more about the servers I support than I do.   

The MAP Tool provides ready-for-presentation reports showing SQL Server counts in use by version, SQL Server counts by Edition, Core licensing, components in use (SSRS, SSAS, SSIS), Windows OS information, as well as detailed information on each database instance, including computer name, physical or virtual, operating system edition, number of cores, number of logical processors, SQL Server version, SQL Server edition, service pack level, CLR version, default collation, security model, and number of users.

In the database details report, you get datatabases by server, dbsize, data file size and name, log file size and name, log file space used %, compatibility level, status, db_owner, created timestamp, last backup, number of tables, views, sprocs, functions, and file-growth characteristics.

Now anyone can code to collect all this data, but when you have to true-up with Microsoft for licensing, the MAP tool puts all the data in a standardized format that Microsoft and their licensing partners are familiar with and it will make your job so much easier.  I do not recommend coding to collect all of this information unless you are simply looking to broaden your coding sills.



Jeff Bennett

St. Louis, MO


Wednesday, March 11, 2015 - 1:15:37 PM - jeff_yao Back To Top (36495)

@ThmasFranz, thanks for your comments.

I heard of MAPT but never used it. But regarding your concerns that a DBA will not know all the server names, I'd agree to some extent. The reason is if there is no list of server names (I mean hosting machine names) to a DBA's knowledge, it means you do not need to manage it. In another word, when I join a new company, my manager or my DBA team will definitely inform me the list of server names. They may tell you what version/services/editions are there, but the server names will be known to me in one way or another (such as via AD)

Wednesday, March 11, 2015 - 12:27:59 PM - Jeff Bennett Back To Top (36494)

Definitely use the MAPP tool provided free from Microsoft.   Captures everything you need to know, with the possible exception of core counts on some older versions of SQL.  Download it.   You will need domain level privileges to get the most out of it, but it will come in handy when you true up annually with Microsoft for licensing.


Jeff Bennett

Missouri, US

Wednesday, March 11, 2015 - 4:40:03 AM - Thomas Franz Back To Top (36483)

If you are new to a company you will probably not know all the server names and even if you are working for years in a company there is a chance that there are some long forgotten SQL servers running in some dusty corners in your data center.

To get a list of all (running) servers / instances and some other important informations (as versions) you could use the free Microsoft Assessment and Planning Toolkit ( It scans the whole network for all installations of the selected product (pick SQL Server before running it).

get free sql tips
agree to terms