Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A T-SQL script that renders media wiki markup of a list of columns in a table that can be used when creating a data dictionary in a media wiki.
-- For more information about this script and how to use it, visit:
-- http://colinmackay.co.uk/2012/06/15/creating-a-data-dictionary-with-sql-server-and-mediawiki-part-two-a-list-of-columns/
-- Edit these parameters to suit your needs.
DECLARE @catalogue SYSNAME = 'AdventureWorks';
DECLARE @schema SYSNAME = 'Production'
DECLARE @table SYSNAME = 'Product'
DECLARE @column SYSNAME;
DECLARE @isNullable SYSNAME;
DECLARE @dataTYpe SYSNAME;
DECLARE @charLength INT;
DECLARE @constraint NVARCHAR(4000);
DECLARE @constraints NVARCHAR(4000);
DECLARE @pkLinks NVARCHAR(4000);
DECLARE @pkColumn SYSNAME;
DECLARE @pkTable SYSNAME;
DECLARE @pkSchema SYSNAME;
DECLARE @ordinalPosition int;
SET NOCOUNT ON
PRINT '{| 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
'
DECLARE table_cursor CURSOR FOR
SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = @catalogue
AND TABLE_SCHEMA = @schema
AND TABLE_NAME = @table
ORDER BY ORDINAL_POSITION
OPEN table_cursor
FETCH NEXT FROM table_cursor
INTO @column, @isNullable, @dataType, @charLength, @ordinalPosition
WHILE @@FETCH_STATUS = 0
BEGIN
SET @constraints = '';
DECLARE constraint_cursor CURSOR FOR
SELECT tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
AND tc.TABLE_CATALOG = kcu.TABLE_CATALOG
AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
AND tc.TABLE_NAME = kcu.TABLE_NAME
WHERE tc.TABLE_CATALOG = @catalogue
AND tc.TABLE_SCHEMA = @schema
AND tc.TABLE_NAME = @table
AND kcu.COLUMN_NAME = @column
OPEN constraint_cursor
FETCH NEXT FROM constraint_cursor
INTO @constraint
WHILE @@FETCH_STATUS = 0
BEGIN
IF LEN(@constraints) = 0
BEGIN
SET @constraints = @constraint
END
ELSE
BEGIN
SET @constraints = @constraints + ', ' + @constraint;
END
FETCH NEXT FROM constraint_cursor
INTO @constraint
END
CLOSE constraint_cursor;
DEALLOCATE constraint_cursor;
SET @pkLinks = '';
DECLARE pk_cursor CURSOR FOR
SELECT PK.TABLE_NAME AS PK_TABLE,
PT.COLUMN_NAME AS PK_COLUMN,
PK.TABLE_SCHEMA AS PK_SCHEMA
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
INNER JOIN (
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE FK.TABLE_NAME = @table
AND CU.COLUMN_NAME = @column
AND FK.TABLE_SCHEMA = @schema
AND FK.TABLE_CATALOG = @catalogue
OPEN pk_cursor
FETCH NEXT FROM pk_cursor
INTO @pkTable, @pkColumn, @pkSchema
WHILE @@FETCH_STATUS = 0
BEGIN
IF LEN(@pkLinks) <> 0
BEGIN
SET @pkLinks = @pkLinks + ', ';
END
SET @pkLinks = @pkLinks + '[['+@catalogue+'.'+@pkSchema+'.'+@pkTable+'|'+
CASE WHEN @pkSchema = @schema THEN @pkTable ELSE @pkSchema+'.'+@pkTable END
+']].'+@pkColumn;
FETCH NEXT FROM pk_cursor
INTO @pkTable, @pkColumn, @pkSchema
END
CLOSE pk_cursor;
DEALLOCATE pk_cursor;
PRINT '|-'
PRINT '| '+ @column
PRINT '| '+ CAST(@ordinalPosition AS NVARCHAR(10))
PRINT '| '+ @isNullable
PRINT '| '+ @dataType + CASE WHEN @charLength IS NULL THEN '' ELSE '('+
CASE WHEN @charLength = -1 THEN 'MAX' ELSE CAST(@charLength AS NVARCHAR(10)) END +')' END
PRINT '| '+ @constraints
PRINT '| '+ @pkLinks
PRINT '| '
FETCH NEXT FROM table_cursor
INTO @column, @isNullable, @dataType, @charLength, @ordinalPosition
END
PRINT '|}'
CLOSE table_cursor;
DEALLOCATE table_cursor;
SET NOCOUNT OFF
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.