Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2010-02-01   |   Comments (5)   |   Related Tips: More > Database Design

Problem

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.

Solution

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)

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.


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.


Summary

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


Last Updated: 2010-02-01


next webcast button


next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips




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.



    



Monday, August 08, 2011 - 10:09:23 AM - Greg Robidoux Back To Top

Hi Tim G, thanks for the tip.  We have an older tip related to Enterprise Manager to do what you suggest.  http://www.mssqltips.com/tip.asp?tip=1004

If you are interested in writing a new tip related to SSMS please let us know here: contribute.asp

Thanks
Greg


Monday, August 08, 2011 - 10:00:39 AM - Tim G Back To Top

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 03, 2010 - 10:58:34 AM - jerryhung Back To Top

 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 01, 2010 - 9:33:31 AM - siddhumehta Back To Top

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.

 --Siddharth.


Monday, February 01, 2010 - 7:49:24 AM - HilaryH11 Back To Top

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


Learn more about SQL Server tools