Extended Property History

  • 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.

    Executive Junior Cowboy Developer, Esq.[/url]

  • 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
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey - Friday, September 14, 2018 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.

    Thanks for the advice (I can't believe I overlooked just tracking it in source control), and I'll check out the tool.

    Executive Junior Cowboy Developer, Esq.[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply