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.

list-columns-as-wiki-markup.sql

-- 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