Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How does Enterprise Manager get its data


By:   |   Last Updated: 2006-06-20   |   Comments   |   Related Tips: More > Tools

Problem
Have you ever wondered where Enterprise Manager gets its information from and how you can retrieve the same data without using Enterprise Manager?  Well it's not a big secret. Most of the information that is displayed in the GUI is based on information gathered from queries, but what queries?  If you have ever looked at the system tables to try to figure this out, it is not as straight forward as you think.  So how do you find out what queries Enterprise Manager is using?

Solution
One simple way of figuring this out is to use Profiler to capture the queries that are being executed and then you can use these same queries to gather the same information.  It is a pretty straight forward process to use Profiler even if you have never used Profiler before.

The first step is to launch Profiler, this can be done one of two ways:

  • Run Profiler (profiler.exe) either from the command line or from the menu
  • or
  • Launch Profiler from Enterprise Manager -- Tools -> SQL Profiler

Once you have Profiler launched you need to create a new trace.  To do this follow the steps below.

  1. File -> New -> Trace
  2. Connect to the server and database
  3. You can adjust the options in the first three tabs or leave them as they are. 
  4. The only item you really need to change is the Filters tab to only select activity from Enterprise Manager.  This shows up in Profiler as "MS SQLEM". See picture below.
  5. Then select Run

The following screen shows the queries that are being used by Enterprise Manager to display data about the Northwind database as I clicked through the GUI.

 

Based on the output from Profiler we can see the query that Enterprise Manager uses to get a list of tables for a database.  To make the output easier to read I copied the query from Profiler and then used the SQL Formatter Tool to format the query.  Below is the formatted query.

SELECT   S1 = O.NAME,
         S2 = USER_NAME(O.UID),
         O.CRDATE,
         O.ID,
         N'SystemObj' = (CASE
                           WHEN (OBJECTPROPERTY(O.ID,N'IsMSShipped') = 1) THEN 1
                           ELSE OBJECTPROPERTY(O.ID,N'IsSystemTable')
                         END),
         O.CATEGORY,
         0,
         OBJECTPROPERTY(O.ID,N'TableHasActiveFulltextIndex'),
         OBJECTPROPERTY(O.ID,N'TableFulltextCatalogId'),
         N'FakeTable' = (CASE
                           WHEN (OBJECTPROPERTY(O.ID,N'tableisfake') = 1) THEN 1
                           ELSE 0
                         END),
         (CASE
            WHEN (OBJECTPROPERTY(O.ID,N'IsQuotedIdentOn') = 1) THEN 1
            ELSE 0
          END),
         (CASE
            WHEN (OBJECTPROPERTY(O.ID,N'IsAnsiNullsOn') = 1) THEN 1
            ELSE 0
          END)
FROM     DBO.SYSOBJECTS O,
         DBO.SYSINDEXES I
WHERE    OBJECTPROPERTY(O.ID,N'IsTable') = 1
         AND I.ID = O.ID
         AND I.INDID < 2
         AND O.NAME NOT LIKE N'#%'
ORDER BY S1,
         S2

Next Steps

  • Download the trace template and unzip
  • Launch Profiler and use this trace template or use your own
    • File - > New -> Trace
    • Connect to the server and database
    • On the first tab select the file you downloaded for the "Template File Name"
    • Then select Run
  • Start clicking away on Enterprise Manager to see what is actually happening behind the scenes


Last Updated: 2006-06-20


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools