HOW TO: Print-off a SQL Server 2008 Database Schema

Overview

This little utility can read either a Microsoft SQL Server 2008 database schema, or a Microsoft Access database schema, and generate an HTML file documenting the structure of the tables, along with some statistical information about some of the columns. Here is a sample of what the output looks like. The entire C# project is available as a zip file containing both source code and executable binaries. I have made it available under a BSD style license, please respect my copyright.

General Use

To use the utility, simply open an *.mdf or *.mdb file with the program. You can do that by specifying a path to the database from the command line, or by just running the program and using the open file dialog it presents to select a database file. Then, you will be prompted about where to save the resulting HTML output. Make sure you save it in a folder with the provided dbSchema.css file or it will be pretty bland. The CSS file provided will italicize the primary key(s), bold the required fields, hide the min / max columns, and put page breaks between database tables. I have made an effort to provide plenty of css classes to the HTML so the look and feel of the resulting document should be entirely customizable. Have fun!

Disclaimers

Of course, you use this utility at your own risk, yadda yadda yadda. But let me make one actual warning about this program: it calculates min/max/count statistics on every column of every table, so don’t run it against a LARGE, active database. If you want to crunch on a big production database, extract a backup copy to a development server and run the numbers against that — don’t say I didn’t warn you. It took a couple of minutes of number crunching to spit out info for the database I updated the program for, and it has a hundred or so tables of various sizes a handful of which have a million or so records in them. That should give you a ballpark figure about how much load is put on the server when it milks your data into a nice little report.

something about how to connect it to SQL Server 2008.

Discussion

I recently pulled out an old program that I wrote in VB6 for printing off an access database table structure just to see how much my coding style has changed over the last 6 or 7 years. I decided to rewrite the whole thing using C# on the .Net 3.5 framework using the OleDB data provider, and then abstracted it a bit and added a second class that can pull from a SQL 2008 express database. It uses SQL Server EXPRESS but with a little bit of tweaking of the code (especially the connection string) it could easily pull from a true Microsoft SQL 2008 Server, and probably a 2005 server.

The first thing I noticed while rewriting the program was the shift in focus. In the old version, the real “work” was spent interacting with the printer – getting the fonts right, the bolding to make it easier to read, the page breaks, columns, avoiding table / field orphaning. The newer version completely avoided that whole ball of wax. Instead, I opted to output a simple html file and use CSS to get the page breaks and fonts looking exactly the way I like them. Focusing instead on just tackling the problem using elegant code, it was actually a fun project rather than just tedious work.

I found that I made extensive use of LINQ, which seemed odd when I thought about it because I couldn’t use use LINQ to SQL (what most developers usually use LINQ for) because that would require that I knew the DB schema before I wanted to print it off, and that sort of destroys the purpose of writing a general purpose database schema printing program.

There is no “LINQ to OleDB” or “LINQ to ODBC” and there certainly isn’t a “LINQ to MySQL” or “LINQ to Oracle.” And, even though there is a LINQ to SQL, and I am technically trying to print off the schema of a SQL Server database, LINQ to SQL still isn’t an option because LINQ to SQL is really more of a “LINK” to a specific database schema, NOT a general LINK to any unknown SQL database, and certainly not as a way to discover the database layout at runtime.

At its very core, LINQ isn’t about database queries, it is really a lot more extensive than that. LINQ is a language for interacting with SETS of OBJECTS. I made use of Linq to DataSets, Linq to XML, and plain old LINQ to Objects. This particular project was a lot of fun, because it reminded me just how much I love LINQ, and not just as a substitute for SQL. I especially like the new XElement XML functions that came out with to Linq to XML they are quite a treat to work with (especially if you remember the old school XML document API).

The entire XML document that loops through all the tables, and all the fields, along with columns about each field is generated from a single statement using no loops whatsoever. Its really cool. Instead of using loops, it uses the XElement constructor that can handle a set of child elements as one of its parameters, combined with a LINQ statement to generate that set of elements in a single statement. Here is the statement that spits out the html file:

output.WriteLine(
  new XElement("html",
    new XElement("head",
      new XElement("title", "Database Schema"),
      new XElement("link",
        new XAttribute("rel", "stylesheet"),
        new XAttribute("type", "text/css"),
        new XAttribute("href", "dbSchema.css"),
        new XAttribute("media", "all"))),
    new XElement("body",
      new XElement("div", new XAttribute("id", "container"),
        from TableInfo table in reader.getSchema()
        select
        new XElement("div", new XAttribute("class", "dbtable"),
          new XElement("h1", table.TableName, new XAttribute("class", "tablename")),
          String.IsNullOrEmpty(table.Description) ? null :
          new XElement("h2", table.Description, new XAttribute("class", "tabledesc")),
          new XElement("table", new XAttribute("class", "tablecolumns"),
            new XElement("tr",
              new XElement("th", "Column Name", new XAttribute("class", "columnname")),
              new XElement("th", "Type", new XAttribute("class", "datatype")),
              new XElement("th", "Reference", new XAttribute("class", "reference")),
              new XElement("th", "Min", new XAttribute("class", "range")),
              new XElement("th", "Max", new XAttribute("class", "range")),
              new XElement("th", "Count", new XAttribute("class", "count")),
              new XElement("th", "Nulls", new XAttribute("class", "nulls")),
              new XElement("th", "Description", new XAttribute("class", "description"))),
            from col in table.Columns
            let isPk = table.PrimaryKeyFields.Contains(col.ColumnName)
            select
            new XElement("tr", new XAttribute("class", "column"
                  + (col.AllowNull ? " allownull" : " notnull")
                  + (isPk ? " primarykey" : "")
                  + (string.IsNullOrEmpty(col.ReferenceTo) ? "" : " foreignkey")),
              new XElement("td", col.ColumnName,
                new XAttribute("class", "columnname" )),
              new XElement("td", new XAttribute("class", "datatype"),
                col.DataType,
                col.MaxLength <= 0 || col.MaxLength > 16*1024 ? "" : "(" + col.MaxLength.ToString() + ")"),
              new XElement("td", nbsp(col.ReferenceTo), new XAttribute("class", "reference")),
              new XElement("td", nbsp(col.Stats.Min), new XAttribute("class", "range")),
              new XElement("td", nbsp(col.Stats.Max), new XAttribute("class", "range")),
              new XElement("td", nbsp(col.Stats.Count), new XAttribute("class", "count")),
              new XElement("td", nbsp(col.Stats.NullCount), new XAttribute("class", "nulls")),
              new XElement("td", nbsp(col.Description), new XAttribute("class", "description"))
            )
          )
        )
      )
    )
  ).ToString()
);

Yes, it does a lot in a single statement, but it isn’t hard to read. It “flows” in the same basic layout as an html document, and it reads more or less like a bunch of single lines – one for each element. But in truth, it is a single statement that creates an entire, complex html file in a single call to the constructor of the root element. The entire XML tree is created in its entirety and written out to the file without ever being assigned into a variable — without any element of the HTML assigned to a variable before being added to the tree.

Summary

Sometimes I need a utility that lets me just print off a database schema in a readable format to set it on the desk next to me while I develop against the database and use it as the authoritative reference. This is the utility I developed for that purpose. Let me know if you’d like to see it updated to allow printing the table structures from another database engine.

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically to your feed reader.

Comments

No comments yet.

Leave a comment

(required)

(required)