Create a Simple SQL Server Database Server Object Report with Minimum Effort

By:   |   Comments (4)   |   Related: > Database Design


Developers and/or Database Administrators (DBAs) are often asked to create a report of different database objects on a database server. I have been evident of situations where people start writing code at the last minute to collect details about different database objects and then the results are sent in the form of Excel spreadsheets. Also such reports are one of the requirements in release notes for any build.

In this tip we will discuss how we can create a last minute report, with almost no coding effort for basic details about the database objects limiting the requirement of coding efforts for only advanced level details.


SQL Server Management Studio (SSMS) is the basic tool used generally for GUI based administration in SQL Server. Object Explorer provides an organized contextual view of different database objects on the database server. Almost everyone who has worked with SQL Server 2005 / SQL Server 2008 knows this basic fact. But most often overlooked is the potential of the object explorer details window for retrieving information about the database objects.

Generally in a typical database server report, which may be used for any variety of purposes like release notes, health monitoring, auditing, or management information, some of the most important database objects that one is required to profile are Databases, Tables and Stored Procedures. There can be more options depending upon the context, some other database objects may also be important to a project, but for the sake of discussion we would discuss these three.

Gathering Data

  • Open SSMS, and connect to your database.
  • In the left-hand pane you will be able to see object explorer and on the right-hand pane you will be able to see the object explorer details pane if your view is the normal default view.
  • Click on databases, which would change the context in the object explorer details.
  • Right-Click on the column headers in the object explorer details and you should be able to see a list as shown in the figure below.

(note: click on images to see larger version)

Object Explorer Details

If you have not seen this before, you will be surprised to see that all these details are available. Select all the details you need by checking or un-checking from the available list. You can also change the order of columns in the object explorer details window by dragging a column header to the position you want.

Below are screenshots of sample reports for Databases, Tables and Stored Procedures that can be created in such manner with reasonable details.

Databases Report

Tables Report

Stored Proc Report

Load Data to Excel

The final question that still remains is how to get this report into a presentable format as screen-print is not a professional option although it can be used.

We have a solution for that too!

  • Click on any object in the object explorer window and select everything by pressing Ctrl + A.
  • Copy this by pressing Ctrl + C and then paste it in an excel sheet. This works wonderfully well as seen in the figure below.
  • One worksheet can be created per database object type. Even if details of more than one database are needed to be captured, it can be appended in the same database object worksheet and filters can be used or manual formatting can be done to distinguish between different servers.

Save To Excel


On thing to note is that this is a one time activity. Once you setup the view, even after you close SSMS and open again you will find the view you created is persisted. So instead of spending your time and energy, and server resources for querying and extracting this information, object explorer details can be easily used for the same purpose.

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 Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

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, August 8, 2011 - 10:00:39 AM - Tim G Back To Top (14339)

This is a good tip for beginners.  The next level up is ask the question: how does management studio get this information?

To learn that, on one's pc, go start, all programs, microsoft sql server 2008 r2, performance tools, profiler.  Connect to your server and run the standard trace against it.   Bring up management studio and then look for the table information again. Return to the profiler trace and  see the sql statements that are used to extract this data programatically.

Wednesday, February 3, 2010 - 10:58:34 AM - jerryhung Back To Top (4844)

 Correct, this ability to [customize column headers] is ONLY available in SSMS 2008 CLIENT tools

 However, it will work on SQL 2000/2005/2008 servers

Monday, February 1, 2010 - 9:33:31 AM - siddhumehta Back To Top (4829)

This tip has been tested on SSMS 2008, and it's a mistake on my part that I should have clearly mentioned the same. These properties are the enhancements that are available with SSMS 2008, and the same were not avaiable to the best of my knowledge in SSMS 2005.

If you are browsing SQL Server 2005 DBs in SSMS 2008, it would still provide the same properties as they are a feature of SSMS 2008. To facilitate the same in SSMS 2005, one can use the Custom Reporting feature (for which you need to develop your own SSRS reports) or freeware products like DBA Dashboard.


Monday, February 1, 2010 - 7:49:24 AM - HilaryH11 Back To Top (4828)

I could only get this to work in SSMS 2008. Does it work in SSMS 2005? Are more steps required?

get free sql tips
agree to terms