ExpressSQL

TSQL scripts for optimizing storage, documentation, and general administrative needs.

View the Project on GitHub LowlyDBA/ExpressSQL

sp_doc

License SQL Server Azure SQL

Build status GitHub Workflow Status (branch) codecov

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 selfdocumenting database! By building the tool in TSQL, the documenting process can remain simple, secure, require no additional infrastructure, and avoid red tape that third party applications often require.

It documents:

and plays nice with:

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 for extended properties on objects. Default is 'Description'.
@LimitStoredProcLength BIT no Limit stored procedure contents to 8000 characters to avoid memory issues with some IDEs. Default is 1.
@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';

To generate the markdown file more quickly:

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

Advanced Use

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'

Sample

Output for the WideWorldImporters database.

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

Contributing

Missing a feature? Found a bug? Open an issue to get some :heart:

More

Check out the other scripts in the Express SQL Suite.