By: Siddharth Mehta | Comments (4) | Related: > 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
- Try out selecting different details from the object explorer details window for different database objects.
- Create reports for these database objects for more than one server and check out how Excel filters can be used in such reports.
- Check out these other tips related to reporting on database objects
About the author
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
