Phil Factor shows us how we can use Extended Properties to build database documentation:
Once you’ve got into the habit of using Extended Properties to document your database, there are obvious benefits:
- You can explain why you added that index or modified that constraint.
- You can describe exactly what that rather obscure column does.
- You can add a reasoned explanation to the use of a table.
You will often need these explanations because, sadly, DDL code isn’t ‘self-documenting’, and human memory is fallible. Extended Properties are easily searched because they are all exposed in one system view.
It is great to add explanations to lists of procedures, functions and views once the database becomes sizeable. Extended Properties are useful when exploring the metadata, but the requirement isn’t quite so essential because comments are preserved along with the source code. Tables, however, are a big problem because SQL Server throws away the script that produces the table, along with all the comments. The reason that this happens is that there are many ways you can alter parts of a table without scripting the entire table. How could one infallibly preserve all these ALTER statements in the preserved script? It’s tricky. Table scripts that you get from SSMS or script via SMO are therefore synthesised from the system tables but without those comments or even Extended Properties.
Extended Properties are useful, but I think the lack of tooling around them prevented widespread adoption. Now that there are a few tools which support them (including SSMS’s data classification mechanism), I wonder if these will get a second look.