>

Quickly automate SQL Server database documentation to Markdown

  sqlserver

Sometimes there’s a need to create database documentation - an old fashioned list of tables, columns, views etc. - as part of delivering a project, or for analysts, project managers and end users.

In these situations I tend to automate. SQL Server is very “meta” in that it contains tables and records that describe, well, other tables and records. Coupled with the MS_Description extended property, it’s a powerful way to keep up-to-date documentation in close proximity to the code itself.

The script below will generate Markdown for database tables, views, stored procedures and functions. Column names and data types are listed for tables. View definitions are output. For stored procedures and functions, only the MS_Description extended property will be output.

Additionally, the Markdown contains hyperlinked table names, useful for referencing from other documents.

I built and adapted the script from sources on the internat. Feel free to build and adapt as you need!

Thomas’s “but it worked for me” disclaimer: before using any code you find on the internet, especially on this blog, take time to understand what the code does and test, test, test. I’m not responsible for damage caused by code from this blog, and don’t offer any support or warranty.


--database documentation queries for current database, outputs markdown format
--outputs:
-- * for each database table, a table with column name, type, description
-- * for each view, the first 3,000 characters of the view definition
-- * for functions and stored procedures, the name and the description
--tested with SQL Server 2019
--run this query with "Results to Text"
--can be copy/pasted to app that supports markdown e.g. static site/blog, GitHub, Confluence etc.
--adapted from https://gist.github.com/mwinckler/2577364, https://www.red-gate.com/hub/product-learning/flyway/managing-database-documentation-during-flyway-based-development, https://gist.github.com/aplocher/fa86d16d3dd94ab4e42cc22b6b2fafa0

SET NOCOUNT, XACT_ABORT ON
SET CONCAT_NULL_YIELDS_NULL OFF

--temp table for lines to output, returned at end of process
DECLARE @temp_lines TABLE (
   --internal identifier used for ordering
   [id] INT IDENTITY (1, 1) PRIMARY KEY,
   --line of SQL code
   [line] NVARCHAR(4000)
)
--temp table for list of tables from INFORMATION_SCHEMA.TABLES
DECLARE @temp_all_tables TABLE (
   [id] INT IDENTITY (1, 1) PRIMARY KEY,
   [TABLE_SCHEMA] SYSNAME NOT NULL,
   [TABLE_NAME] SYSNAME NOT NULL
)
--temp table for just primary keys
DECLARE @temp_primary_keys TABLE (
   [TABLE_SCHEMA] SYSNAME NOT NULL,
   [TABLE_NAME] SYSNAME NOT NULL,
   [COLUMN_NAME] SYSNAME NOT NULL
)

--insert disclaimer into output in italics
--date in unambiguous month format d/MMM/YYYY (because, Aussie here)
INSERT INTO @temp_lines ([line])
SELECT  N'*This page was generated from a script at ' + FORMAT(GETDATE(), 'hh:mmtt d/MMM/yyyy') + N'*'

--get tables into temp table (will not include system tables)
INSERT INTO @temp_all_tables
SELECT  [TABLE_SCHEMA], [TABLE_NAME]
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_TYPE = 'BASE TABLE'
ORDER BY 1, 2

--get just primary keys into temp table for easier retrieval & comparison later
--need table schema, table name, primary key column(s)
INSERT INTO @temp_primary_keys
SELECT  [TABLE_SCHEMA] = CONVERT(SYSNAME, SCHEMA_NAME(o.[schema_id])),
       [TABLE_NAME] = o.[name],
       --column name
       [COLUMN_NAME] = c.[name]
FROM    sys.objects o INNER JOIN
           --only objects with indexes
           sys.indexes i ON
               o.object_id = i.object_id INNER JOIN
           sys.index_columns ic ON
               i.object_id = ic.object_id AND
               i.index_id = ic.index_id INNER JOIN
           sys.columns c ON
               ic.object_id = c.object_id AND
               ic.column_id = c.column_id
WHERE   --user (not system) tables only
       o.[type] = 'U' AND
       --ignore system tables
       o.[is_ms_shipped] = 0 AND
       --ignore heaps
       i.[index_id] > 0 AND
       --ignore indexes that "...cannot be used directly as a data access path. Hypothetical indexes hold column-level statistics..."
       i.[is_hypothetical] = 0 AND
       --primary keys only
       i.[is_primary_key] = 1

--current table loop index (start at 1, unless there's no tables)
DECLARE @current_loop_table_index INT = (SELECT MIN([id]) FROM @temp_all_tables)

--output section heading for tables
INSERT INTO @temp_lines ([line])
SELECT  N'## Tables'

--loop through each table
WHILE @current_loop_table_index IS NOT NULL BEGIN
   --current loop schema and table name
   DECLARE @current_loop_table_schema SYSNAME = (SELECT MIN([TABLE_SCHEMA]) FROM @temp_all_tables WHERE [id] = @current_loop_table_index)
   DECLARE @current_loop_table_name SYSNAME = (SELECT MIN([TABLE_NAME]) FROM @temp_all_tables WHERE [id] = @current_loop_table_index)

   --output schema & table name as heading 3, make into an anchor
   INSERT INTO @temp_lines ([line])
   SELECT  N'### [' + @current_loop_table_schema + N'.' + @current_loop_table_name + N'](#' + @current_loop_table_schema + N'.' + @current_loop_table_name + N')'

   --output extended property for table, allow up to 2,000 characters
   --adapted from https://gist.github.com/mwinckler/2577364
   INSERT INTO @temp_lines ([line])
   SELECT  --if there's no trailing full stop, add one
           LTRIM(RTRIM(CONVERT(NVARCHAR(2000), [value]))) +
           CASE
               WHEN RIGHT(LTRIM(RTRIM(CONVERT(NVARCHAR(2000), [value]))), 1) != N'.' THEN N'.'
               ELSE N''
           END
   FROM    sys.extended_properties
   WHERE   --MS_Description metadata only
           [name] = N'MS_Description' AND
           --should be zero for table and column metadata
           [minor_id] = 0 AND
           --match object ID of current loop table
           [major_id] = OBJECT_ID(QUOTENAME(@current_loop_table_schema) + N'.' + QUOTENAME(@current_loop_table_name))

   --output markdown table header for columns
   INSERT INTO @temp_lines ([line])
   SELECT  N'| Column name | Description | Data type | Allow NULLs |'
   UNION ALL
   SELECT  N'| ------- | ------- | ------- | ------- |'

   --output columns from INFORMATION_SCHEMA.COLUMNS
   --as markdown table rows
   --roughly match SSMS designers
   --data types in uppercase, because that's the way I like 'em
   INSERT INTO @temp_lines ([line])
   SELECT  --column name as bold text
           N'| **' + COLUMNS.[COLUMN_NAME] + N'**' +
           --if this column is a primary key, add star symbol (ideally could use "key" emoji)
           CASE
               WHEN EXISTS (
                     SELECT  1
                     FROM    @temp_primary_keys p
                     WHERE   COLUMNS.[TABLE_SCHEMA] = p.[TABLE_SCHEMA] AND
                             COLUMNS.[TABLE_NAME] = p.[TABLE_NAME] AND
                             COLUMNS.[COLUMN_NAME] = p.[COLUMN_NAME]
                    ) THEN N' ★'
               ELSE N''
           END +
           --get description for extended properties
           --if there's no trailing full stop, add one
           N' | ' + LTRIM(RTRIM(CONVERT(NVARCHAR(2000), ep.[value]))) +
           CASE
               WHEN RIGHT(LTRIM(RTRIM(CONVERT(NVARCHAR(2000), ep.[value]))), 1) != N'.' THEN N'.'
               ELSE N''
           END +
           --put together data type
           N' | ' + UPPER(COLUMNS.[DATA_TYPE]) +
           --append precision in brackets, for certain data types only
           CASE
               --add precision for datetime offset
               WHEN UPPER(COLUMNS.[DATA_TYPE]) IN (N'DATETIMEOFFSET') THEN N'(' + CONVERT(NVARCHAR(25), COLUMNS.[DATETIME_PRECISION]) + N')'
               --for numeric columns, add precision
               WHEN UPPER(COLUMNS.[DATA_TYPE]) IN (N'NUMERIC') THEN N'(' + CONVERT(NVARCHAR(25), COLUMNS.[NUMERIC_PRECISION]) + N',' + CONVERT(NVARCHAR(25), COLUMNS.[NUMERIC_SCALE]) + N')'
               --for character columns, add length (replace length of -1 with MAX)
               WHEN UPPER(COLUMNS.[DATA_TYPE]) IN (N'CHAR', N'NCHAR', N'VARCHAR', N'NVARCHAR') THEN
                   REPLACE(N'(' + CONVERT(NVARCHAR(25), COLUMNS.CHARACTER_MAXIMUM_LENGTH) + N')', N'(-1)', N'(MAX)')
               ELSE N''
           END +
           --special case - if an IDENTITY column, append word "identity"
           CASE
               WHEN c.[is_identity] = 1 THEN N' IDENTITY'
               ELSE N''
           END +
           --custom NULL column, ticked check box if is nullable
           N' | ' + CASE
               WHEN COLUMNS.[IS_NULLABLE] = N'YES' THEN N'☑'
               --empty check box if not nullable
               ELSE N'☐'
           END +
           --close table row
           N' |'
   FROM    INFORMATION_SCHEMA.COLUMNS LEFT OUTER JOIN
               sys.extended_properties ep ON
                   ep.[name] = N'MS_Description' AND
                   --table is "major_id"
                   ep.[major_id] = OBJECT_ID(QUOTENAME(@current_loop_table_schema) + N'.' + QUOTENAME(@current_loop_table_name)) AND
                   --column number is "minor_id"
                   ep.[minor_id] = COLUMNS.[ORDINAL_POSITION] LEFT OUTER JOIN
               --sys.columns, needed for identity columns
               sys.columns C ON
                   C.[object_id] = OBJECT_ID(QUOTENAME(@current_loop_table_schema) + N'.' + QUOTENAME(@current_loop_table_name)) AND
                   C.[name] = COLUMNS.[COLUMN_NAME]
   WHERE   COLUMNS.[TABLE_SCHEMA] = @current_loop_table_schema AND
           COLUMNS.[TABLE_NAME] = @current_loop_table_name
   ORDER BY
           --order the same as SSMS designers
           COLUMNS.[ORDINAL_POSITION]

   --increment table loop index
   SET @current_loop_table_index = (SELECT MIN([id]) FROM @temp_all_tables WHERE [id] > @current_loop_table_index)
END

--are there any views?
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.VIEWS) BEGIN
   --output section heading for views
   INSERT INTO @temp_lines ([line])
   SELECT  N'## Views'

   --for views, output name as heading, and definition as code block
   INSERT INTO @temp_lines ([line])
   SELECT  N'### ' + [TABLE_SCHEMA] + N'.' + [TABLE_NAME] + CHAR(13) +
           --if definition is longer than 4,000 characters, will be NULL
           CASE
               WHEN [VIEW_DEFINITION] IS NULL THEN N'*Text too large to display*'
               ELSE
                   --start code block
                   N'````' + CHAR(13) +
                   --first 3,000 characters of definition
                   --need to remove leading and trailing linebreaks
                   TRIM(CONVERT(NVARCHAR(3000), TRIM(CHAR(13) FROM TRIM(CHAR(10) FROM TRIM(CHAR(13) FROM [VIEW_DEFINITION]))))) +
                   --if definition is longer than 3,000 characters, add ellipses
                   (CASE WHEN LEN([VIEW_DEFINITION]) > 3000 THEN N'...' ELSE N'' END) + CHAR(13) +
                   --end of code block
                   N'````' + CHAR(13)
           END
   FROM    INFORMATION_SCHEMA.VIEWS
   ORDER BY [TABLE_SCHEMA], [TABLE_NAME]
END

--are there any (non-system) stored procedures?
IF EXISTS(SELECT * FROM sys.procedures WHERE [is_ms_shipped] = 0) BEGIN
   --output section heading for stored procedures
   INSERT INTO @temp_lines ([line])
   SELECT  N'## Stored procedures'

   --stored procedures, output name and description separated by line break
   INSERT INTO @temp_lines ([line])
   SELECT  N'**' + procs.[name] + N'**' + CHAR(13) + LTRIM(RTRIM(CONVERT(NVARCHAR(2000), ep.[value])))
   FROM    sys.procedures procs LEFT OUTER JOIN
               sys.extended_properties ep ON
                       ep.[name] = N'MS_Description' AND
                       ep.[major_id] = procs.[object_id] AND
                       --stored procedure comments (not parameters)
                       ep.[minor_id] = 0
   WHERE   --not system stored procs
           procs.[is_ms_shipped] = 0
   ORDER BY procs.[name]
END

--are there any functions?
IF EXISTS(SELECT * FROM sys.objects WHERE [type] = 'FN' AND [is_ms_shipped] = 0) BEGIN
   --output section heading for functions
   INSERT INTO @temp_lines ([line])
   SELECT  N'## Functions'

   --functions, output name and metadata separated by line break
   INSERT INTO @temp_lines ([line])
   SELECT  N'**' + o.[name] + N'**' + CHAR(13) + LTRIM(RTRIM(CONVERT(NVARCHAR(2000), ep.[value])))
   FROM    sys.objects o LEFT OUTER JOIN
               sys.extended_properties ep ON
                       ep.[name] = N'MS_Description' AND
                       ep.[major_id] = o.[object_id] AND
                       ep.[minor_id] = 0
   WHERE   --functions
           o.[type] = 'FN' AND
           --not system functions
           o.[is_ms_shipped] = 0
   ORDER BY o.[name]
END

SET NOCOUNT OFF

--return output in order of line number, but without line number in output
SELECT  [line]
FROM    @temp_lines