Tuesday, August 28, 2012

Get the size of all databases

...without using dynamic SQL or sp_msForEachDB

A common task for DBA's is to manage the size of all the databases on one or more instances. This often requires the ability to collect DB sizes using T-SQL; most methods I have found involve Dynamic SQL or sp_msForEachDB. These methods work but, in each case, leaves you vulnerable to SQL Injection.

SQL Server 2005 introduced the sys.master_files catalog view. sys.master_files contains everything you need for this (log and data file sizes) except for the database name. That is okay, because it does provide the database_id which can be used for joining to sys.databases which contains the database_id and database name.

This query will get you all db sizes without any Dynamic SQL:

Get All Database Sizes:

Monday, August 20, 2012

My Reusable, Element-Name Agnostic, XML to HTML table XSLT Transform - Part 1

Special Thanks

Like I said in my first post - this is my first blog. The best experience so far has been seeing where traffic to my site is coming from. Let me extend a special "Hello" to my readers in Hong Kong, Germany, Russia, Australia, South Africa, Indonesia, England, India (Hi Anu), Chicago, Bolingbrook, Woodridge and France. How cool is the Internet?!?! You say something in Chicago and someone listens in Bavaria. In some cases it's just one person but I will take it    :^))..


Ahhh... XML and SQL. As I have said before, none of this (blogs, the ability to communicate and collaborate globally with ease using the Internet) would be possible if not for the wonders of XML and SQL. I have written some posts about SQL but nothing yet about XML. Today that changes as I introduce my Reusable, Element-Name Agnostic, XML to HTML table XSLT Transform.

What it Does

You can attach any XML file that has the same format/hierarchey structure as an HTML table and attach it to my transform to create an HTML table populated with the with your XML data. It accomplishes this very quickly, regardless of the element names, using some XSLT best practices and with approx 30 lines of XSLT code.

It will take this...

Source XML data

... and turn it into this...

XML data transformed into HTML table

... which, in a HTML browser, will produce this

HTML in browser

... and will do so regardless of the names used in the XML file passed to it.


When doing XML XSLT development (or any kind of development) one of my primary objectives is to accomplish the task at-hand with as little code as possible. This makes debugging easier and reduces the footprint for potential human error. My rule of thumb is “no solution is correct if a more elegant solution is available.” By elegant I mean: surprisingly simple yet highly effective. If you can get the same performance and accurate results then you have more work to do.

To demonstrate I will start by taking a basic transform that performs this task for a specific XML file/structure with static element names and re-write it so that it is re-usable and takes advantage of the functional programming power of XSLT.

Example XML (Catalog.xml)

I took this right from W3Schools.com. This is the file they use in many of their examples.


Desired Result (truncated for readability)

Title Artist Company Country Price Year
Empire Burlesque Bob Dylan USA Columbia 10.90 1985
Hide your heart Bonnie Tyler UK CBS Records 9.90 v1988
Greatest Hits Dolly Parton USA RCA 9.90 1982

Original Transform (Transform V1)

This is the transform before applying some XSLT development best practices to it. It is simple and easy-to-understand but can be improved. 

Transform V1:

Newly Updated Transform

Below (Transform V7) is the finished product. The most notable change is that I removed the FOR-EACH loop for building columns(td) and rows(tr); using FOR-EACH is not the best choice in XSLT for iterating through a node set (I still use a FOR-EACH to get the column headings). For the rest we are using XSLT templates. All explicit references to any element names (such as artist, price, year, etc) are removed. Instead the transform has been updated to return values based on their location in the node tree. This is done using path expressions and predicates. Writing transforms in this manner makes them much more reusable because you are not married XML structures based on element names.

Transform V7:

In the next post (Part 2) we will examine how we made the original more effective and reusable.

Updated on 8/27/2012 at 8:49PM