SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Oiling the gears for the data dictionary


Oiling the gears for the data dictionary

Author
Message
Dave Poole
Dave Poole
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16622 Visits: 3403
Comments posted to this topic are about the item Oiling the gears for the data dictionary

LinkedIn Profile
www.simple-talk.com
Satnam Singh
Satnam Singh
SSC Eights!
SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)

Group: General Forum Members
Points: 974 Visits: 1084
Great article David. Keep up the Good Work going. Many Thanks for sharing with us.

Cheers,
Satnam
Vedran Kesegic
Vedran Kesegic
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1934 Visits: 1266
Documenting the database is very important.
You can see descriptions next to the table columns quite easily with XDetails plugin (free): www.sqlxdetails.com


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

James A Skipwith
James A Skipwith
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 723
David,

This is exactly how I do it!

Nice article.

James

James
MCM [@TheSQLPimp]
Kiara
Kiara
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 1426
Very useful article. Thanks very much for writing it.

-Ki
Dennis Wagner-347763
Dennis Wagner-347763
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 228
David, very nice! Of course, it all comes down to someone putting in the descriptions for each field or you just get a list of tables and columns.

I'd be interested in knowing how many DBAs/Developers take the time to actually fill in the descriptions. It would also be very interesting to see how many do this for commercial systems where the client requests a data dictionary vs. internal systems that are only used in house.
ACinAZ
ACinAZ
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 957
I'm part of a project developing an application for states to use when managing various programs. As such, we have to provide a data dictionary as part of the deliverables, but some of the descriptions the developers supplied are things like "this is the ID field" or "disscribs the person" [sic]...Not helpful and not accurate, and shows that, sometimes, developers aren't the best at using English! I created an Excel/VBA tool to pull the table names from the database, create a tab for each table and populate the new worksheet with table and field names, and also the extended properties for each. At each milestone in the project, we deliver the Excel file to the documentation department for "wordsmithing", then another button on the Excel file updates (or inserts) the MS_Description property in the master database. Quick, easy, adapts to changes in the tables, and allows the developers to concentrate on writing code, while the documentation folk get to do what they get paid for.

The only hiccup in your detection of blank extended attributes is when the developer uses "Must populate this" as their description for each and every field in the tables they create! :-)
roger.plowman
roger.plowman
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2415 Visits: 1380
Personally I think the data dictionary approach would be better served using a data modeling tool like Toad Data Modeller or Rational Rose. (I use Toad myself).

That way not only do you have the tables laid out graphically, showing relationships, you also have the comments for each table and field, plus any functions, and if you use the built in domain tools you have consistancy across tables for things like zip codes, phone numbers, etc.

Since I wrote a small Access tool to generate the basic stored procedures and it has the ability to let me create more complex SPs myself (along with notes, etc) I find I don't rely so much on SQL Server for documentation.

IMO a dedicated data modeller is the most logical place for documentation anyway, since it basically is both documentation *and* script creation tool.
dave.clark
dave.clark
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 564
Dennis Wagner-347763 (2/17/2011)
It would also be very interesting to see how many do this for commercial systems where the client requests a data dictionary vs. internal systems that are only used in house.


At my company, we force application developers to submit database requests with descriptions for the objects. This is enforced through the intranet page where they submit the request. Additionally we review the descriptions to make sure they make sense!

The extended properties are released to all of our customers. We also use these properties for an internal data dictionary as well as an external data dictionary that our customers can view by logging on to our customer support web site. Even though the information is in the customer's database we provide the information on the web site for those customers that aren't familiar with getting the information through T-SQL in the database.
matthew.greenslade-881391
matthew.greenslade-881391
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 63
Good article. I am writing some .net code to manage SQL Server Extended Properties across all our servers including managing different 'sets' of extended properties, bulk copying and pasting of properties from database to database, table to table and even field to field.

Its likely to be a few more weeks in production but I can put some details up if I get it running. It doesnt hold properties in a table though, they are dynamically queried.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search