SQL Server Inventory of Services

Posted on 2011-05-02
Last Modified: 2012-08-13
I have 23 SQL Server databases. I need to populate an inventory database that contains information about the SQL Server instances and the machines they are running on.
I would like to create a view that uses t-SQL to poll the instance to find out what SQL Server Services are installed (SSIS, SSRS, Extc.) I am working with SQL Server 2000 and 2005 versions.

How can I do this?  What system view, stored procedure can I use to check for services installed/and or running?
Question by:Bitadmin
    LVL 22

    Expert Comment

    I am currently working on this very problem, myself.  

    For openers, you need to have a login that has access to all of the servers.  Then you you are going to have to develop two separate sets of queries, one for SQL Server 2005 (and later) and one for SQL Server 2000.  I would recommend approaching this from the standpoint of creating an SSIS package (since, hopefully, sometime in the near future you will have migrated your SS2000 databases to SS2005/2008/Denali) to do the processing.

    The manner in which you obtain the data from SQL Server 2000 and SQL Server 2005 (and later) are different.  That leads to some issues with regard to trying to make a single process to acquire the data. As I said, I am currently working on this problem myself.  I have established a means for building a Data Dictionary database on each instance and I am working on an SSIS package to process the various instances and consolidate the data into a separate instance's database.  

    This is not a trivial task that, as far as I know, can be answered with a single query (upon which you could build a view).
    LVL 22

    Accepted Solution

    You may have to determine the answers for each service separately:

    For instance, SS2005's SSRS will require a ReportServer database so you can figure that, if one exists, the SSRS is (probably) installed.  I am not sure about the other services (e.g. SSIS and SSAS).

    select * from sys.databases where name like 'ReportServer%'

    Open in new window


    Author Closing Comment

    Thanks that was very helpful for the SQLServer 2005 instances. As for SQLServer 2000 I will have to dig deeper for those. I only have 7 left anyway the bulk are in 2005.  The sys.databases was a great save

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now