SQL 2008 Reporting Functionality

No I am not talking about reporting services I am talking about the database reporting functionality.

There is a built in reporting function, which can be accessed from the database level, by simply right clicking the database you wish to view reporting on.

reporting

As you can see there are some very useful pre-defined reports, my particular favorite is the ‘Disk Usage By Table’ Only very recently I had to run a query against all tables in a database to find out the disk usage for the tables…. Now you don’t have to write such query’s like:

-- first we need to find the tables
-- using master to query 
-- remember to change databasename
select *
from databasename.information_schema.tables

This above query will retrieve the ‘Table_Catalog’, ‘Table_Schema’, Table_Name’ and ‘Table_Type’

Whilst we don’t need anything apart from the table_name, it’s always handy to have everything, but for this exercise we only want the table_name so copy the contents of the ‘table_name’ dump it into excel, make sure you put it in row B not A, because we need to leave some space for the next query

Place the below query (not the comments just the exec sp_spaceused) into the A row of Excel.

-- here we are calling the stored procedure 'spacedused'
exec sp_spaceused

The fill all the rows down with the above query like:

reporting01

Once you have completed this then you save the file as a .CSV, once you have completed this open notepad and then open the CSV within notepad.

Your notepad file will look similar to:

image

As you can see the query and the table name are separated by a comma, we need to get rid of this, press ctrl + H, this should bring up the find and replace function now in the find type ’spaceused,’ in the replace enter ’spacedused ‘ (note there is a space the comma is now deleted in the replace field)

image

Once you have got this right then, click on ‘Replace All’.
it should now look like:

image

now you are ready to go and paste all of the notepad file in to a SQL query against the database you wish to find the table size data on.

This is what is should look like when it has turned the results

image

If you have more then 100 tables you will have to view the results as text.

As you can see that’s a pretty long winded process, now I know there are some scripts out there that can and will do the same thing, and there are other methods out there, but I find this works well.

To cut a long story short, you no longer need to go through this dubious process any more you simply click on ‘Disk Usage by Table’ simple and it shows you a nicely formatted report, Like:

image

SQL 2008 also has the ability for you to create your own reports through a easy to use report builder not the old one in SQL 2005 but an actual desktop application called  ‘Microsoft Report Designer’, which is also a new feature in itself.


One Response to “SQL 2008 Reporting Functionality”

Leave a Reply