We would like to create a data dictionary for our SQL database, with some description for table and columns. As I understand it can be stored in extended property for the table and columns, then we can pull from it.
Is there any bad effects for adding extended property to columns and tables Othan than it adds some storage spaces?
Thanks
None that I'm aware of. It's actually an excellent place to store that type of info.
Just be sure you don't drop the table, or you'll lose all the ext props!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 1, 2023 at 1:20 pm
I assume there's a point at which extended properties will cause an issue, but I'm not aware of anyone that has hit it using the things in a standard way. I'd go for it. I have gone for it.
The only thing I'd add is, if you really want good documentation on your database, get it into source control. Then, you not only have the table, columns, etc., plus the extended properties, but also a history of changes over time, and audit trail of who exactly did the changes. That's a HUGE leap forward in database management.
"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
March 1, 2023 at 6:30 pm
Thank you Grant. That is good to know. We do have database objects in the source control. yes that is very helpful to keep track of changes.
Any idea what this means I assume there's a point at which extended properties will cause an issue
Also for the extended property some times for tables and columns involve business side to define the description of that, how can we do that? Give access to business analyst to access the database to add extended property, or they can do in MS word, and we as dba copy them and add them in? just wonder what is better solutions, for our database it is not too big and most of columns are self explanatory, so I wonder if they are needed to be added to extended property and uses database spaces, but our BA wants a data dictionary for the db.
Thanks,
March 1, 2023 at 6:35 pm
Just that, as with anything, I'm sure there's a tipping point. "3 extended properties, no big deal, 300, 3000000, something, big deal". I honestly don't know how many zeroes you get there, but I'm sure there's a performance limit, prior to the physical limit. That's all.
"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
March 1, 2023 at 6:36 pm
As to letting anyone edit them, I'd build a small app for it. No sense in exposing more than you need to.
"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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply