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.
and plays nice with:
|@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.|
>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
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'
Output for the WideWorldImporters database.
Note: Slight changes may be made to this database to better demo script capabilities.
Missing a feature? Found a bug? Open an issue to get some
Check out the other scripts in the Express SQL Suite.