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:

;WITH dbs([dbid],size) AS ( SELECT database_id, SUM(size)/128 FROM sys.master_files GROUP BY database_id ) SELECT d2.name db, d1.size [size(mb)] FROM dbs d1 JOIN sys.databases d2 ON d1.dbid=d2.database_id

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    :^))..

Intro

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...

... and turn it into this...

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

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

Background

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.

Catalog.xml

<catalog> <cd> <title>Empire Burlesque</title> <artist>Bob Dylan</artist> <country>USA</country> <company>Columbia</company> <price>10.90</price> <year>1985</year> </cd> <cd> <title>Hide your heart</title> <artist>Bonnie Tyler</artist> <country>UK</country> <company>CBS Records</company> <price>9.90</price> <year>1988</year> </cd> <cd> <title>Greatest Hits</title> <artist>Dolly Parton</artist> <country>USA</country> <company>RCA</company> <price>9.90</price> <year>1982</year> </cd> <cd> <title>Still got the blues</title> <artist>Gary Moore</artist> <country>UK</country> <company>Virgin records</company> <price>10.20</price> <year>1990</year> </cd> <cd> <title>Eros</title> <artist>Eros Ramazzotti</artist> <country>EU</country> <company>BMG</company> <price>9.90</price> <year>1997</year> </cd> <cd> <title>One night only</title> <artist>Bee Gees</artist> <country>UK</country> <company>Polydor</company> <price>10.90</price> <year>1998</year> </cd> <cd> <title>Sylvias Mother</title> <artist>Dr.Hook</artist> <country>UK</country> <company>CBS</company> <price>8.10</price> <year>1973</year> </cd> <cd> <title>Maggie May</title> <artist>Rod Stewart</artist> <country>UK</country> <company>Pickwick</company> <price>8.50</price> <year>1990</year> </cd> <cd> <title>Romanza</title> <artist>Andrea Bocelli</artist> <country>EU</country> <company>Polydor</company> <price>10.80</price> <year>1996</year> </cd> <cd> <title>When a man loves a woman</title> <artist>Percy Sledge</artist> <country>USA</country> <company>Atlantic</company> <price>8.70</price> <year>1987</year> </cd> <cd> <title>Black angel</title> <artist>Savage Rose</artist> <country>EU</country> <company>Mega</company> <price>10.90</price> <year>1995</year> </cd> <cd> <title>1999 Grammy Nominees</title> <artist>Many</artist> <country>USA</country> <company>Grammy</company> <price>10.20</price> <year>1999</year> </cd> <cd> <title>For the good times</title> <artist>Kenny Rogers</artist> <country>UK</country> <company>Mucik Master</company> <price>8.70</price> <year>1995</year> </cd> <cd> <title>Big Willie style</title> <artist>Will Smith</artist> <country>USA</country> <company>Columbia</company> <price>9.90</price> <year>1997</year> </cd> <cd> <title>Tupelo Honey</title> <artist>Van Morrison</artist> <country>UK</country> <company>Polydor</company> <price>8.20</price> <year>1971</year> </cd> <cd> <title>Soulsville</title> <artist>Jorn Hoel</artist> <country>Norway</country> <company>WEA</company> <price>7.90</price> <year>1996</year> </cd> <cd> <title>The very best of</title> <artist>Cat Stevens</artist> <country>UK</country> <company>Island</company> <price>8.90</price> <year>1990</year> </cd> <cd> <title>Stop</title> <artist>Sam Brown</artist> <country>UK</country> <company>A and M</company> <price>8.90</price> <year>1988</year> </cd> <cd> <title>Bridge of Spies</title> <artist>T`Pau</artist> <country>UK</country> <company>Siren</company> <price>7.90</price> <year>1987</year> </cd> <cd> <title>Private Dancer</title> <artist>Tina Turner</artist> <country>UK</country> <company>Capitol</company> <price>8.90</price> <year>1983</year> </cd> <cd> <title>Midt om natten</title> <artist>Kim Larsen</artist> <country>EU</country> <company>Medley</company> <price>7.80</price> <year>1983</year> </cd> <cd> <title>Pavarotti Gala Concert</title> <artist>Luciano Pavarotti</artist> <country>UK</country> <company>DECCA</company> <price>9.90</price> <year>1991</year> </cd> <cd> <title>The dock of the bay</title> <artist>Otis Redding</artist> <country>USA</country> <company>Atlantic</company> <price>7.90</price> <year>1987</year> </cd> <cd> <title>Picture book</title> <artist>Simply Red</artist> <country>EU</country> <company>Elektra</company> <price>7.20</price> <year>1985</year> </cd> <cd> <title>Red</title> <artist>The Communards</artist> <country>UK</country> <company>London</company> <price>7.80</price> <year>1987</year> </cd> <cd> <title>Unchain my heart</title> <artist>Joe Cocker</artist> <country>USA</country> <company>EMI</company> <price>8.20</price> <year>1987</year> </cd> </catalog>

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:

<?xml version="1.0"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="html" version="4.0" encoding="iso-8859-1" indent="no"/> <xsl:template match="/"> <html> <body> <h2>My CD Collection</h2> <table border="1" cellspacing="0"> <tr bgcolor="#bfbfbf"> <th>Title</th> <th>Artist</th> <th>Country</th> <th>Company</th> <th>Price</th> <th>Year</th> </tr> <xsl:for-each select="catalog/cd"> <tr> <td><xsl:value-of select="title" /></td> <td><xsl:value-of select="artist" /></td> <td><xsl:value-of select="country" /></td> <td><xsl:value-of select="company" /></td> <td><xsl:value-of select="price" /></td> <td><xsl:value-of select="year" /></td> </tr> </xsl:for-each> </table> </body> </html> </xsl:template> </xsl:stylesheet>

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:

<?xml version="1.0" encoding="ISO-8859-1"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="html" version="4.0" encoding="iso-8859-1" indent="yes"/> <xsl:template match="/"> <html> <body> <h2><xsl:value-of select="*[1]/@*[1]"/></h2> <table border="1" cellspacing="0"> <tr> <xsl:for-each select="/*/*[1]/*"> <th bgcolor="#bfbfbf"> <xsl:value-of select="local-name()"/> </th> </xsl:for-each> </tr> <xsl:apply-templates /> </table> </body> </html> </xsl:template> <xsl:template match="/*[1]/*"> <tr> <xsl:apply-templates /> </tr> </xsl:template> <xsl:template match="*"> <td> <xsl:apply-templates /> </td> </xsl:template> </xsl:stylesheet>

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