Software Development

Creating a data dictionary with SQL Server and MediaWiki – Part three: inbound references

In my previous posts (parts one and two), I showed how to generate mediawiki markup to generate lists of tables and columns that can be used as the starting blocks of a data dictionary. In this post I continue that by showing how to generate a list of inbound links (i.e. a list of other tables that reference this one)

The script

At the top of the script are three parameters that you need to set to suit your needs. The @catalogue is the name of the database, the @schema and @table are the names of the destination schema and table.

-- Edit these parameters to suit your needs.
DECLARE @catalogue SYSNAME = 'AdventureWorks';
DECLARE @schema SYSNAME = 'Production'
DECLARE @table SYSNAME = 'Product'

DECLARE @fk_catalogue SYSNAME;
DECLARE @fk_schema SYSNAME;
DECLARE @fk_table_name SYSNAME;
DECLARE @fk_column_name SYSNAME;

SET NOCOUNT ON

PRINT '{| class="wikitable sortable"
|-
! scope="col" | Schema
! scope="col" | Table
! scope="col" | Column
! scope="col" class="unsortable" | Notes
'

DECLARE fk_cursor CURSOR FOR
SELECT FK.TABLE_CATALOG, FK.TABLE_SCHEMA, FK.TABLE_NAME, CU.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
	ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
WHERE PK.TABLE_SCHEMA = @schema
AND PK.TABLE_NAME = @table
AND PK.TABLE_CATALOG = @catalogue
ORDER BY FK.TABLE_SCHEMA, FK.TABLE_NAME, CU.COLUMN_NAME

OPEN fk_cursor

FETCH NEXT FROM fk_cursor
INTO @fk_catalogue, @fk_schema, @fk_table_name, @fk_column_name

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT '|-
| '+ @fk_schema+'
| [[' + @fk_catalogue + '.'+@fk_schema+'.'+@fk_table_name + '|'+ @fk_table_name+']]
| '+ @fk_column_name + '
|
'

FETCH NEXT FROM fk_cursor
INTO @fk_catalogue, @fk_schema, @fk_table_name, @fk_column_name
END
PRINT '|}'

CLOSE fk_cursor;
DEALLOCATE fk_cursor;
SET NOCOUNT OFF

How it renders

This is how the resulting mediawiki code renders in the browser.

Rendering of inbound references

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.

Software Development

Creating a data dictionary with SQL Server and MediaWiki – Part one: a list of tables

Background

We’ve just installed MediaWiki as a documentation tool where I work. To get things up and running as quickly as possible, I created a few SQL Scripts to pull out schema information from the SQL Server in order to create a data dictionary. The SQL Scripts produce WikiMedia mark up that allows various things to be cross referenced in a way that makes navigating around the wiki fairly easy.

Getting a list of tables

The first thing to do is to get a list of tables. In the code below, the first line is the only one you’ll need to modify if you want to accept the output in its current format. The first line determines whether you are getting back a list of tables or a list of views.

SET NOCOUNT ON ensures that we don’t get any messages midway through saying things like “(504 row(s) affected)”

The first print statement simple outputs the header for the table in wiki markup. We are also marking certain columns on the table as sortable. That way the user can sort the table to the way they want it rather than the way it was initially rendered.

Then we head into the cursor (as we will be looping over each to the tables in turn outputting wiki mark up for each of them).

In each loop we are outputting various details about the tables. The mark up includes links to a wiki page that will contain more detail about the specific table. Although the rendered wiki table only shows the table name, the underlying link ensures a fully qualified name is used so that you don’t run into clashes between databases or schemas.

Finally, when the loop created by the cursor is complete we put in the closing markup for the wiki table.

There are a few more things going on, but I thought I’d just concentrate on the essentials and not go into a tutorial on how cursors work or anything like that

How it renders

The script renders Wiki Markup, which the MediaWiki engine renders as a table. Using the Adventure Works database as an example, this is what the end result looks like.

Data Dictionary showing a list of tables

The script

The script is now located in a github gist: https://gist.github.com/3035858

Coming up

In the next post, I’ll be showing the script for getting the column information out for each of the individual tables (or views).

Updates

  • This article was updated by adding in the code to generate sortable tables on Friday, 15/June/2012 at 22:00