How to format documentation in MS_Description extendedproperties?
Why use extended properties at all here? They're second class citizens and not all that discoverable. The documentation you've described could just as easily be a block comment in the stored procedure body or, better yet, in your data dictionary and/or source control (since this stuff really should be documented in a safe location beyond the database anyway).
A block comment in a view, function, trigger or stored procedure is definitely stored in the database. Everything between the two GO
batch separators is stored as the body (though I confess I don't know which visual editors might strip them, since I just use a standard query editor):
GO
/*
this is a comment
*/
CREATE PROCEDURE dbo.whatever
/* @A is for apple, @J is for jacks */
@A int,
@J int
AS
BEGIN
SET NOCOUNT ON;
-- do stuff;
END
GO
(I will also confess that comments stored before CREATE PROCEDURE
or after the final END
may be prone to inadvertent exclusion when someone modifies the procedure, so it may be safer to keep the comments inside the body.)
A little trickier to store documentation for tables and columns inside the database without using extended properties. But again, I'd argue these should be documented externally anyway. If you're going to bother writing documentation for these things, you may as well put it somewhere that will still exist if the database goes south.
To my knowledge, none of the tooling that understands MS_Description
also understands XML or Markdown.
I would recommend using Markdown anyways because even unformatted it is quite readable.
Tools such as SSDT and SQL Prompt do actually use MS_Description
.