Almost every application DBA would have a need to document the various database objects such as tables, views, stored procedures and functions. I wasn’t an exception and exploring ways to document the 400+ tables we have. Basically, this would serve the following purpose:
1. Every one reading the document would have a fair idea as to where the table is being used and for what purpose a table was designed.
2. It makes the life easier for the experienced folks and improves productivity. As they no longer have to spend longer time in teaching the new joiners about the database schema.
Thanks to Microsoft, we have a few extended properties that make our life easier in documenting the database schema. I’ve created the below procedure based on few of those extended properties.
The above procedure will add or modify the table or column description accordingly. The modifying as well as inserting logic is handled in the procedure, so just mention the description for the entity, it will overwrite or create the description accordingly.
Following are the steps to add table or column definition:
The following query is used to fetch the table/column description: