Link Search Menu Expand Document

sp_doc

sp_doc map logo

Purpose

You wouldn’t code without comments, so why database without them?

Databases can be complex, disastrous things. Not every database admin, developer, or analyst has the time to learn the ins and outs of a database in order to just do their work. To make things worse, few products and fewer free options exist to help present databases in a human readable format.

sp_doc’s goal is to generate on the fly database documentation in markdown. This means you now have a free and extensible self-documenting database! By building the tool in T-SQL, the documenting process can remain simple, secure, require no additional infrastructure, and avoid red tape that third party applications often require.

It documents:

  • Schemas
  • Tables
    • Triggers
    • Default Constraints
    • Check Constraints
    • Indexes
  • Views
    • Indexes
  • Stored Procedures
  • Synonyms
  • Scalar Functions
  • Inline Table Functions
  • User Defined Table Types
  • Extended Properties
  • Sensitivity Classifications (2019+)

and plays nice with:

  • Github Flavored Markdown
  • Gitlab Flavored Markdown
  • Any other CommonMark based renderer

Arguments

Parameter Type Output Description
@DatabaseName SYSNAME(128) no Target database to document. Default is the stored procedure’s database.
@ExtendedPropertyName SYSNAME(128) no Key used as the main extended property on objects. Default is ‘Description’.
@AllExtendedProperties BIT no Include all extended properties for each object, not just @ExtendedPropertyName.
@LimitStoredProcLength BIT no Limit stored procedure contents to 8000 characters, to avoid memory issues with some IDEs. Default is 1.
@Emojis BIT no Use emojis when generating documentation. Default is 0.
@Verbose BIT no Whether or not to print additional information during the script run. Default is 0.
@SqlMajorVersion TINYINT no Used for unit testing purposes only.
@SqlMinorVersion SMALLINT no Used for unit testing purposes only.

Usage

Basic Use

EXEC [dbo].[sp_doc] @DatabaseName = 'WideWorldImporters'

EXEC [dbo].[sp_doc] @DatabaseName = 'WideWorldImporters', @ExtendedPropertyName = 'MS_Description';

Output to File

Batch

sqlcmd -S localhost -d master -Q "EXEC [dbo].[sp_doc] @DatabaseName = 'WideWorldImporters';" -o readme.md -y 0

Note: The -y 0 option is important to specify so that variable length output is not capped at the default of 256 characters by sqlcmd.

PowerShell / DbaTools

$Query = "EXEC [dbo].[sp_doc] @DatabaseName = 'WideWorldImporters';"
Invoke-DbaQuery -SqlInstance localhost -Database master -Query $Query -As SingleValue | Out-File readme.md

Advanced Use

Stored Procedure Parameters

Add extended properties to programmable objects, using parameter names as keys, to include their descriptions in the documentation:

EXEC sys.sp_addextendedproperty @name=N'@ExtendedPropertyName',
    @value=N'Key for extended properties on objects. Default is ''Description''.' ,
    @level0type=N'SCHEMA',@level0name=N'dbo',
    @level1type=N'PROCEDURE',
    @level1name=N'sp_doc';

Embedded Markdown

Extended properties containing embedded markdown are supported. The following characters are replaced to render markdown as plain text to avoid issues with formatting:

Character Replacement Description
\| | HTML code for pipe
` ` HTML code for tick
Newline <br/> HTML tag for line break

Output

View WorldWideImporters demo

Note: Slight changes may be made to this database to better demo script capabilities.

Known Issues

Missing Line Breaks

When executing in SSMS, even with ‘Retain CR/LF on copy or save’ setting enabled, line breaks may incorrectly not appear in the results. A UserVoice bug exists for this bug - please :arrow_up: vote if you agree it should be addressed.

This should not affect the markdown rendering, but it is recommended to use another application for execution until this is fixed.