Click here to Skip to main content
15,860,972 members
Articles / Web Development / ASP.NET
Article

Web control to generate database design documents in HTML

Rate me:
Please Sign up or sign in to vote.
4.59/5 (11 votes)
24 Jan 2006MIT2 min read 83.9K   1.8K   64   14
Just 200 lines of VB.NET to document your SQL Server databases.

Introduction

This code is a server control to publish your database structure (tables, columns, views, triggers, and stored procedures) on the web. It is written in VB.NET but could easily be written in any other language. The control can be nested in any ASPX page, to provide a nicely formatted database design document that is always up-to-date and available online.

DBreporter screenshot

Background

SQL Server stores all database object definitions in its system tables; therefore, it is easy to query it like any other set of database tables. In my example, the relevant dictionary tables are:

  • sysobjects (contains names of tables, views, stored procedures, and triggers).
  • syscolumns (contains column and stored procedure parameter names).
  • systypes (contains column types as displayed in Enterprise Manager).
  • syscomments (contains SQL scripts of views, stored procedures, and triggers).

Partial diagram of SQL Server dictionary:

SQL Server dictionary

Another web control doing the same thing (in C# and XSLT instead of VB.NET) was published by Jose A. Gonzalvo. Because I'm using a different algorithm, and also because I am documenting stored procedures and trigger SQL scripts, I will offer my code to the community.

Using the code

This web control must be nested in an ASPX page like any other web control. It has a property called SqlConnection that specifies the database connection string. The user must be granted read permission to the system tables.

The sample project which comes with it includes a style sheet (with classes RowHeader, RowOdd, RowEven, and SQL) as well as a few pictures to make it look sexy. Do not forget to include them in your project.

The code

My code is divided in three parts:

  • Querying the dictionary
  • Organizing the dataset in a hierarchy
  • Rendering the HTML

The dictionary can be queried in different ways. Using SQL Server 2000, I wrote the following SQL:

VB
'### Objects (Tables, Views, Stored Procedures, Triggers) ###

sql = "SELECT id, name, rtrim(xtype) as xtype " & _
      "FROM sysobjects SO " & _
      "WHERE SO.status>-1 AND SO.xtype in ('U','V','P','TR') " & _
      "ORDER BY name;"

'### Columns or Parameters ###

sql &= "SELECT SC.id, SC.name, SC.isnullable, ST.name AS typename, " & _
       "CASE WHEN SC.xtype = 231 THEN SC.length/2" & _ 
       " ELSE SC.length END AS length " & _
       "FROM systypes ST, syscolumns SC, sysobjects SO " & _
       "WHERE SC.xtype=ST.xtype " & _
         " AND SC.id=SO.id" & _
         " AND SO.status>-1 AND SO.xtype in ('U','V','P') " & _
         " AND ST.length<>256 " & _
       "ORDER BY SO.name, SC.colid;"

'### SQL Scripts ###

sql &= "SELECT SC.id, SC.text AS sql " & _
       "FROM syscomments SC, sysobjects SO " & _
       "WHERE SO.id=SC.id" & _
         " AND SO.status>-1 AND SO.xtype in ('TR','V','P'); "

To handle data more efficiently, I submit my three queries at once and retrieve the result into a single DataSet.

Building the hierarchy and transforming the DataSet into XML is done as follows:

VB
ds.DataSetName = "dbreport"
ds.Tables(0).TableName = "entity"
ds.Tables(1).TableName = "column"
ds.Tables(2).TableName = "definition"
MaxLoop = ds.Tables.Count - 1
For i = 0 To MaxLoop
    With ds.Tables(i)
        For j = 0 To .Columns.Count - 1
            .Columns(j).ColumnName = LCase(.Columns(j).ColumnName)
            .Columns(j).ColumnMapping = MappingType.Attribute
        Next
        .Columns("id").ColumnMapping = MappingType.Hidden
    End With
Next
With ds
    Dim rel1 As DataRelation = .Relations.Add("entity-column", _
        ds.Tables("entity").Columns("id"), _
        ds.Tables("column").Columns("id"))
    rel1.Nested = True
    Dim rel2 As DataRelation = .Relations.Add("entity-definition", _
        ds.Tables("entity").Columns("id"), _
        ds.Tables("definition").Columns("id"))
    rel2.Nested = True
End With
myDOM.LoadXml(ds.GetXml)

The resulting XML will be as follows:

XML
<dbreport>
  <entity name="Alphabetical list of products" xtype="V">
    <column name="ProductID" isnullable="0" 
               typename="int" length="4" />
    <column name="ProductName" isnullable="0" 
               typename="nvarchar" length="40" />
    <column name="SupplierID" isnullable="1" 
               typename="int" length="4" />
    <column name="CategoryID" isnullable="1" 
               typename="int" length="4" />
     ...
    <definition sql="create view "Alphabetical list of products" 
            AS SELECT Products.*, 
            Categories.CategoryName FROM Categories 
            INNER JOIN Products ON Categories.CategoryID = 
            Products.CategoryID 
            WHERE (((Products.Discontinued)=0))" />
   </entity>
   ...
</dbreport>

From this XML document, it is now easy to generate HTML. I do it, using System.Text.StringBuilder, to do string concatenations of HTML tags with the elements and attributes of my XML document. This last transformation could have been done using XSLT as well.

Notes

To get column lengths, instead of querying syscolumns.length, I use the following SQL:

SQL
CASE WHEN SC.xtype = 231 THEN SC.length/2 ELSE SC.length END AS length

in order to be consistent with Enterprise manager which displays nvarchar as the string length rather than the space it takes to store it.

For more information, a detailed description of SQL Server system tables is available on the MSDN web site.

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
United States United States
I'm a UI engineer with an eye for UX and a passion for model-driven UIs.

I usually build UIs for startups in the San Francisco Bay Area.

My hobby open source project is Evolutility, a minimalist low-code platform with a model-driven UI, a model-driven backend, and a set of models to play with.

More about me on my GitHub page.

Comments and Discussions

 
QuestionMy 5 Pin
Mardani Dani5-Jan-12 13:00
Mardani Dani5-Jan-12 13:00 
AnswerRe: My 5 Pin
Olivier_Giulieri5-Jan-12 14:00
Olivier_Giulieri5-Jan-12 14:00 
GeneralOther web control w/ complementary features Pin
Olivier_Giulieri20-May-10 5:50
Olivier_Giulieri20-May-10 5:50 
GeneralDiplaying database (in C#) in website (in html) Pin
22teddy2226-Mar-07 7:38
22teddy2226-Mar-07 7:38 
GeneralType 'DBreporter.DBreporter' is not defined. Pin
madval1-Feb-06 10:23
madval1-Feb-06 10:23 
GeneralRe: Type 'DBreporter.DBreporter' is not defined. Pin
Olivier_Giulieri2-Feb-06 18:13
Olivier_Giulieri2-Feb-06 18:13 
GeneralRe: Type 'DBreporter.DBreporter' is not defined. Pin
madval3-Feb-06 5:22
madval3-Feb-06 5:22 
GeneralRe: Type 'DBreporter.DBreporter' is not defined. Pin
Olivier_Giulieri7-Feb-06 19:06
Olivier_Giulieri7-Feb-06 19:06 
GeneralRe: Type 'DBreporter.DBreporter' is not defined. Pin
madval8-Feb-06 5:58
madval8-Feb-06 5:58 
QuestionDid I Miss Something? Pin
Wayne W31-Jan-06 2:05
Wayne W31-Jan-06 2:05 
I downloaded and extracted the project but the DBDocs.aspx.vb file isn't there? How can I get that?

-------
Wayne
AnswerRe: Did I Miss Something? Pin
Olivier_Giulieri31-Jan-06 5:02
Olivier_Giulieri31-Jan-06 5:02 
GeneralNot so good for SQL Server 2005 Pin
DeKale25-Jan-06 9:53
DeKale25-Jan-06 9:53 
GeneralRe: Not so good for SQL Server 2005 Pin
Olivier_Giulieri28-Jan-06 16:22
Olivier_Giulieri28-Jan-06 16:22 
GeneralRe: Not so good for SQL Server 2005 Pin
DeKale29-Jan-06 1:40
DeKale29-Jan-06 1:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.