One of the features of SSMS in SQL Server 2005 that was a clear improvement over Enterprise Manager and Query Analyzer in 2000 was the introduction of built-in reports at the Server and Database levels. These graphical reports provided out-of-the-box functionality regarding meta-data. At first, Microsoft did not release the corresponding .RDL files so that we could customize these reports. But since SQL Server 2005 SP2, we can download the report source code and develop custom reports of our own making.
Accessing meta-data is somewhat tricking in SQL Server 2005. The system tables have been locked down, even for administrators, and we are forced to use system catalog views and dynamic management views instead. This is fine, since most are fully documented in Books Online. However, many times we are left in the position of writing complex joins to get the information we need.
For instance, running the Index Physical Statistics report in SSMS 2008 shows a four view join using the DMV called sys.dm_db_index_physical_stats (the name just rolls off the tongue doesn’t it?…what was wrong with DBCC SHOWCONTIG?). This DMV is joined with the system catalog views sys.objects, sys.schemas and sys.indexes to produce the report highlighting fragmentation statistics.
Wouldn’t it be nice if we could take the Microsoft canned reports in RDL format and customize them to our own needs using Reporting Services without having to start from scratch?
Well now you can:
Here’s a tech note that describes the concepts:
http://msdn.microsoft.com/en-us/library/bb153684.aspx
And here’s how to download the sample reports delivered in SSMS:
http://blogs.msdn.com/sqlrem/archive/2006/08/30/SSMS-Reports-3.aspx
cheers
Brian
Recent Posts:
Universal Health Care for SQL Server? SQL Profiler - 2008 style...