On one of my projects we had to re-use an existing table. Unfortunately the table wasn’t documented very well(read; not at all). To make it even worse some of the columns were used for different things than you would expect based on the column names. The table even contained some implicit logic about the way the data was inserted into the table.
To avoid this horror again, we decided to apply the boy scout rule and leave the camp place in a better state. We started by adding documentation to changed or newly added columns using the SQL Server Extended Properties feature.
You can add Extended Properties either through SQL Server Management Studio or through the sp_addextendedproperty stored procedure:
exec sp_addextendedproperty
@name = N'Price'
,@value = N'Testing entry for Extended Property'
,@level0type = N'Schema', @level0name = 'northwind'
,@level1type = N'Table', @level1name = 'Product'
,@level2type = N'Column', @level2name = 'Price'
go
A few parameters are required to execute sp_addextendedproperty.
- @name is ‘Price’ in our case. This cannot be null. This is the name of the Extended Property.
- @value is the value or description of the property and it cannot exceed 7500 bytes.
- @level0type in our case ‘Schema’ and @level0name is the value is set as 'northwind' as the value
- @level1type in our case ‘Table’ and @level1name is ‘Product’
- @level2type in our case ‘Column’ and @level2name is ‘Price’
More information: https://www.mssqltips.com/sqlservertip/5384/working-with-sql-server-extended-properties/