I also don't use only extended properties to store such info because it's way too easy to lose the data. Developer drops and recreates a table and poof, it's all gone!
1 Like
Backup
Indeed ... however, you need to discover that the data has gone missing in a timely fashion in order to be able to recover it. You then need to figure out when it went missing, in order to be sure to catch any data recently changed before the accidental deletion happened. Harder to delete data from my metadata tables, as there is some protection from foreign keys etc. and audit tables (which log deletions, using triggers), whereas if I stored all that in Extended Properties it would be much easier for it to be deleted potentially without anyone becoming aware for quite some time. I hadn't really thought of that as being a Pro/Con thing, as my Metadata Tables came first, and then tying them to the sys.columns data, using a single extended property with a Unique ID came later.
I use tables because I also generate the resulting metadata in different formats. I like to use simple html pages with links to view table, etc., documentation, so I gen those. And since it's from a table, it's easy to specify just a subset of data to be built, for a particular program, system, etc..
SQL Server 2016 introduced temporal tables in which data rows when changed, the previous row data is written to a history table with a begin and end system timestamp. In the past we've had to create a trigger to accomplish this. This allows querying for what the data looked like at a given time. Nothing can be permanently lost and an audit trail can be established if the table contains a modified_by column capturing the SUser_Name() executing the DML command.
Of course this is another table solution. However with this solution the process can be automated with a DDL trigger capturing metadata into a temporal table from system metadata views and extended properties.
We're talking about metadata -- such as column descriptions, heading, etc. -- not table data.
I was alluding to automating the capture of metadata by tracking changes to extended properties and system metadata views into (a) temporal table(s) using DDL Triggers.