September 13, 2018 at 4:30 pm
Hi SSC,
I've been messing around with extended properties for documenting various objects, and I'd really like to be able to store the history of a given property (and/or object). Obviously I could forsake true extended properties altogether and roll my own from scratch, but I really like using native functionality, and not having to introduce yet another bespoke system someone has to know about to use from server to server.
The cleanest approach I can think of is to create a wrapper around sp_addextendedproperty, sp_updateextendedproperty and sp_dropextendedproperty which will handle things like archiving for me. Before I go too far down this rabbit hole, does anyone know if there's a simpler way to do this I'm not thinking of?
These may be horrible ideas, but examples might be something with triggers, or re-defining the system stored procedures (is that even a thing?), etc.
September 14, 2018 at 5:41 am
I'd suggest using source control on all the database to track changes. That way, any change made to the extended properties goes through source control, you have versions. That's going to be way easier than trying to build an intercept in front of internal processes. Another approach could be to use a compare tool (I know of one) to capture the scripts on a regular basis and store them somewhere (again, probably source control works best for this). I'd do either of these than try to capture the changes live.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 14, 2018 at 10:57 pm
Grant Fritchey - Friday, September 14, 2018 5:41 AMI'd suggest using source control on all the database to track changes. That way, any change made to the extended properties goes through source control, you have versions. That's going to be way easier than trying to build an intercept in front of internal processes. Another approach could be to use a compare tool (I know of one) to capture the scripts on a regular basis and store them somewhere (again, probably source control works best for this). I'd do either of these than try to capture the changes live.
Thanks for the advice (I can't believe I overlooked just tracking it in source control), and I'll check out the tool.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply