Problem
There are no free solutions available to create HTML data dictionaries of SQL server databases.
Solution
Using a combination of CSS, jQuery, CFCs, static file making, created a neat update-able tool.
Detailed explanation
Well here's the basic story, I remember years ago, oracle or sql dba's showing off some fancy data dictionaries, which were basically either Flash or HTML pages, that showed different parts, properties of a database.
I always wanted to create my own, but I wanted to see what was possible, and the best approach to do it.
For design, I looked at many different CSS data table examples, and I chose a basic example, out of this really nice CSS data table example page, http://icant.co.uk/csstablegallery/, which had a nice gallery of CSS stylesheets to select from.
But like most of you, I was more interested in the data gathered, and how to make data dictionaries.
Well one of the techniques, I remember from in the 1990's was the making of static ColdFusion pages, or HTML pages, based out of dynamic ColdFusion pages. This allows you to have fast loading static pages, for pages that did't have lots of data changing.
So what I wanted was to create simple static pages of:
1. database properties
2. table properties
3. view properties
4. procedure properties.
Then I started to look at the examples of other data dictonaries, and started to write queries to help generate the different data aspects.
select a.name, b.table_name, object_id(b.table_name) as object_id
from master..sysdatabases a
inner join information_schema.tables b on b.table_catalog = a.name
order by a.name asc, b.table_name asc
For example the query above will give you a list of tables, the database they are in, and the table's object id. Which was used in some of my other queries, later on.
Then I built several different CFC's to handle generating static pages for each level from db to procedures.
It took me a long while, to make each work right.
Also to handle the navigation I am using jQuery's treeview plugin, which didn't exactly make for pretty results, but made the pages much easier to navigate.
So much to really discuss here. But I can discuss more later, or you can download free my sqldoc tool at riaforge.com
Here's the link http://sqldoc.riaforge.com.
I'll freely admit this is all diamond in the rough writing and coding, but it get's the job done and for me that's what matters most!
+
This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License. Permissions beyond the scope of this license, pertaining to the examples of code included within this work are available at Adobe.
