Software Development

Creating a data dictionary with SQL Server and MediaWiki – Part two: a list of columns

In my previous post, I showed how to create a SQL Script that generates Mediawiki mark-up to create a list of tables (or views) in your database. In this post, I’ll continue with generating a list of columns.

Script walk through

At the top of the script are three variables used to define which table (or view) from which we want the columns. The @catalogue is the name of the database, the @schema and @table are the schema name (often simply ‘dbo’) and the table or view name, respectively.

After a little housekeeping (defining all the variables we’ll be using later) we output the head of the wiki table. This includes the instructions that it is to be sortable, and on which columns the sort is happening.

Then we open our first cursor. The table_cursor walks through all the columns in the table.

Inside the loop of the table_cursor, we create a constraint_cursor to get the constraints for each column by building up the @constraints variable with the details which will be put on the wiki table later.

After the that, a new cursor (pk_cursor) is set up to find the primary key end points of the foreign key column that is being rendered.

Finally, all the information that has been collected is output to the screen.

Once the table_cursor is over the mark-up for closing off the wiki table is output.

How it renders

The wiki mark-up looks like this:

{| class="wikitable sortable"
|-
! scope="col" | Column Name
! scope="col" | Ordinal Position
! scope="col" | Is Nullable
! scope="col" | Data type
! scope="col" | Constraints
! scope="col" | FK Endpoint
! scope="col" class="unsortable" | Notes
|-
| ProductID
| 1
| NO
| int
| PRIMARY KEY
|
|
|-

And it renders to the screen like this:

Mediawiki representation of the columns in a table

The script

The script can be found in full as a gist on github: https://gist.github.com/3035964.

Coming up

Next, I’ll be showing creating a list of inbound references to the table.

One thought on “Creating a data dictionary with SQL Server and MediaWiki – Part two: a list of columns

  1. Hi Colin,

    This is a very useful series of articles, thank you! One question: have you had any success embedding the Pt2: script for a list of columns within the Pt1: script for list of tables? It works well now but requires a bit of copy/pasting to document an entire DB. Here is the output I am looking for:

    ==Tables==

    {| class=”wikitable sortable”
    |-
    ! scope=”col” | Schema
    ! scope=”col” | Table
    ! scope=”col” class=”unsortable” | Notes
    |-
    | dbo
    | [[#TestTable1|TestTable1]]
    |
    |-
    | dbo
    | [[#TestTable2|TestTable2]]
    |
    |}

    ==Columns==

    ===TestTable1===

    {| class=”wikitable sortable”
    |-
    ! scope=”col” | Column Name
    ! scope=”col” | Ordinal Position
    ! scope=”col” | Is Nullable
    ! scope=”col” | Data type
    ! scope=”col” | Constraints
    ! scope=”col” | FK Endpoint
    ! scope=”col” class=”unsortable” | Notes
    |-
    |
    |
    |
    |
    |
    |
    | }

    ===TestTable2===

    {| class=”wikitable sortable”
    |-
    ! scope=”col” | Column Name
    ! scope=”col” | Ordinal Position
    ! scope=”col” | Is Nullable
    ! scope=”col” | Data type
    ! scope=”col” | Constraints
    ! scope=”col” | FK Endpoint
    ! scope=”col” class=”unsortable” | Notes
    |-
    |
    |
    |
    |
    |
    |
    | }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s