Generate a complete markdown data dictionary template, including a table of contents, for Azure DevOps wiki from a list of tables. Will also display PK and FK info

data-dict-gen.sql

use my-database
go

declare @tableList table (Id int not null primary key identity, DatabaseName varchar(50), SchemaName varchar(50), TableName varchar(50), TableDescription varchar(max))

-- #### CONFIGURATION:

-- Which tables to generate the markdown for?
insert into @tableList (DatabaseName, SchemaName, TableName, TableDescription) values
('my-database', 'dbo', 'Entity', ''),
('my-database', 'dbo', 'Person', ''),
('my-database', 'dbo', 'Address', '')

declare 
-- Extra markdown header indent levels
	@headerOffset int = 0

-- #### END CONFIGURATION

declare 
	@markdownLines table (Id int not null primary key identity, Line varchar(max), Sequence int)

declare 
	@extraHeaderLevels varchar(20) = '',
	@i int = 0

while (@i < @headerOffset)
begin
	set @extraHeaderLevels = @extraHeaderLevels +'#'
	set @i = @i + 1
end

insert into @markdownLines values (@extraHeaderLevels + '# Data Dictionary', 50)
insert into @markdownLines values ('', 50)
insert into @markdownLines values (@extraHeaderLevels + '## Table Overview', 50)
insert into @markdownLines values ('', 50)
insert into @markdownLines values ('| Database | Schema | Table | Notes |', 50)
insert into @markdownLines values ('|-|-|-|-|', 50)
insert into @markdownLines values ('', 100)
insert into @markdownLines values (@extraHeaderLevels + '## Tables', 100)

declare @tmpTablelist table (DatabaseName varchar(50), TableName varchar(50))

insert into @tmpTablelist (DatabaseName, TableName)
	select DatabaseName, SchemaName +'.'+ TableName from @tableList

declare @pks table (colname varchar(100))
declare @fks table (colname varchar(100), refTable varchar(100), refCol varchar(100))

while exists (select * from @tableList)
begin
	declare 
		@tableId int,
		@databaseName varchar(50),
		@schemaName varchar(50),
		@tableName varchar(50),
		@tableDescription varchar(max)

	select top 1 
		@tableId = Id,
		@databaseName = DatabaseName,
		@schemaName = SchemaName,
		@tableName = TableName ,
		@tableDescription = TableDescription
	from @tableList
	order by Id

	delete from @pks
	delete from @fks
	
	insert into @pks (colname) 
		select k.COLUMN_NAME
		from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as c
			join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as k 
				on c.TABLE_NAME = k.TABLE_NAME
					and c.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG
					and c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
					and c.CONSTRAINT_NAME = k.CONSTRAINT_NAME
		where c.CONSTRAINT_TYPE = 'PRIMARY KEY' and k.TABLE_NAME = @tableName and k.TABLE_SCHEMA = @schemaName

	insert into @fks(colname, refTable, refCol)
		select
			col_name(fk.parent_object_id, fk.parent_column_id) as colname,
			object_schema_name(fk.referenced_object_id) +'.'+ object_name(fk.referenced_object_id) as refTable,
			col_name(fk.referenced_object_id, fk.referenced_column_id) as refCol
		from sys.foreign_key_columns as fk
		where fk.parent_object_id = object_id(@schemaName +'.'+ @tableName)

	-- Sequence 50 = "Table Overview"
	insert into @markdownLines values ('| '+ @databaseName +' | '+ @schemaName +' | ['+ @tableName +'](#'+ @databaseName +'.'+ @schemaName +'.'+ @tableName +') | ' + case when isnull(@tableDescription, '') = '' then '' else '*'+ @tableDescription +'*' end + ' |', 50)

	-- Sequence 100 = Table Details / Columns List
	insert into @markdownLines values 
		 ('', 100)
		,(@extraHeaderLevels + '### '+ @databaseName +'.'+ @schemaName +'.'+ @tableName, 100)
		,('', 100)

	if (isnull(@tableDescription, '') <> '')
	begin
		insert into @markdownLines values 
			('*'+ @tableDescription +'*', 100)
			,('', 100)
	end

	insert into @markdownLines values 
		('| Name | Type | Nullable | Other Properties | Notes |', 100)
		,('|-|-|-|-|-|', 100)

	-- Add column details
	insert into @markdownLines
		select
			/* Column 1: Column name (and key icon if PK) */
			'| ' + case when c.name in (select * from @pks) then ':key: ' else '' end 
				+ c.name 
			/* Column 2: Data type */
			+ ' | ' + t.name 
				/* include 'length' for these datatypes. e.g. varchar(50): */
				+ case when t.name in ('varchar', 'char', 'nvarchar', 'nchar') then '('+ cast(c.length as varchar(50)) +')' else '' end 
			/* Column 3: Is null */
			+ ' | ' + case when isnullable = 1 then 'yes' else 'no' end 
			/* Column 4: Other properties ('PK', 'FK (ReferencedTable)' */
			+ ' |' 
				+ case when c.name in (select colname from @pks) then ' PK' else '' end
				+ case when c.name in (select colname from @fks) then ' FK ('
					/* include the referenced schema / table in the note */
					+ (case when (select refTable from @fks where colname=c.name) in (select TableName from @tmpTablelist) then
						/* if the FK references a table in our data dictionary, link to it */
						'[' 
						+ (select refTable from @fks where colname=c.name) 
						+'](#' 
						+ (select DatabaseName +'.'+ TableName from @tmpTablelist where TableName in (select refTable from @fks where colname=c.name))
						+ ')'
					else
						/* if the FK is not one in our data dictionary, show text without a link */
						(select refTable from @fks where colname=c.name)
					end)
				+')' else '' end
			+ ' | |' as md, 
			100
		from sysobjects as o
			join syscolumns as c on o.id=c.id
			join systypes as t on t.xtype=c.xtype
		where o.id=object_id(@schemaName +'.'+ @tableName)
		order by c.colid

	delete from @tableList where Id = @tableId
end

select Line from @markdownLines order by Sequence, Id
go