Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment