“Documentation” The bane of our existence and we all hate creating it, don’t we?! I am always searching for ways to improve documentation through automation. Awhile ago I stumbled upon utilizing the extended properties of sql objects to store change management info. The T-SQL script at the bottom of the page produces a simple data dictionary for a SQL Server Database, along with descriptions of its objects. It produces HTML output that can be uploaded to a web server, or you could copy and paste into Word. The example includes tables, columns and stored procedures, but it would be fairly simple to tweak the query to include additional objects such as views, users, primary and foreign keys etc. This technique can be modified and enhanced to store almost any type of metadata you want.
Before running the query, it is highly recommended to add descriptions to the tables, columns and procedures. You can do this within SQL Server Management Studio (right click the object, select Properties and then the Extended Properties tab), but I find it easier to use the built-in sys.sp_addextendedproperty procedure.
Adding a Description to a Table:-
Adding a Description to a Column:-
Now we can generate the documentation….