InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 06:35:57 GMT20http://www.sqlservercentral.com/Forums/Topic1065439-60-1.aspx[quote] The documentation tools so are seem to be focused on getting data out of extended properties, not necessary making the population of those values practical.[/quote]At this point I feel I have to mention the Red-Gate SQL Doc is a tool that does make maintenance of the extended properties easier and no, I don't work for Red-Gate.Since I wrote this article it has been hammered home to me just how important this stuff is. Let us suppose that you are a standard e-commerce site such as Amazon. Yes, your revenue comes from selling items but when you consider data you have so many other revenue streams available to you:-1. Driving cross-sell/upsell and next logical products2. Selling data to your suppliers3. Selling data to government organisations. For example, in the UK we have the Office for National Statistics. A website with a broad product and customer footprint can inform governments of buying trends and spending habits (consumer confidence being in the news a lot a present)I know of some catalogue retailers who liaise with the police to help spot/track fraudsters.All this is only possible if you have a catalogue of your data, including its lineage. You are on a hiding to nothing if the information is locked up in peoples heads.Wed, 30 Nov 2011 15:45:29 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic1065439-60-1.aspx[quote][b]Dennis Wagner-347763 (2/17/2011)[/b][hr]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.[/quote]This is my situation and struggle. Our database schema & objects are maintained in individual .sql files that go into TFS so figuring out a way to document the schema has been a challenge. Using sp_addExtendedProperty and sp_UpdateExtendedProperty system stored procedures to add commentary feels overly verbose and a lot of work, especially when the .sql source is hand crafted. I just don't see that being done. Plus, if you are looking at the .sql source they provide no assistance in understanding what a particular column is. Then throw in a need to potentially have internal facing descriptions & external (customer) facing descriptions.In my research so far this has been the first useful article that I've come across. The documentation tools so are seem to be focused on getting data out of extended properties, not necessary making the population of those values practical.Wed, 30 Nov 2011 11:27:56 GMTdonrhttp://www.sqlservercentral.com/Forums/Topic1065439-60-1.aspxI have just finished a data warehouse project where the design was completed and signed off my the users responsible for the business areas involved. The key point was that they needed to understand what they were signing and if they didn't, then those items were not permitted to be deployed for development. Practically, this meant that all tables and columns had meaningful business descriptions which everyone (IT professionals and our users) could understand.All design work was done in a database agnostic tool (in our case Enterprise Architect by SPARX System). The meta data associated which all tables and columns was included in the DDL generated from the design repository. The meta data was combined with the actual usage of each column (in cubes, reporting services models and reporting services reports) and this was stored in a single enriched meta data repository - a set of tables used to stored this info and the links between each item. This made finding where terms (business phrases, columns etc) were used so easy. This was a major point of distinction between this project and most others I have been involved with. The key point being that the meta data was central to the process and was always considered to be a key deliverable from the project.Tue, 22 Feb 2011 20:36:53 GMThappycat59http://www.sqlservercentral.com/Forums/Topic1065439-60-1.aspxNice to know I'm not the only one who bothers with this stuff.The key points I was hoping to address were[li]Make it a trivial task to use and maintain[/li][li]Make it part of the build script for continuous integration[/li]Perhaps the most important part is to get a business stakeholder to be a driver of the process. If business profitability relies on being able to explore and analyze data then you have to know what you are exploring and you can only do this with a useful data dictionary.For me agile doesn't say "no documentation" it says "only produce documentation that has business value".Thu, 17 Feb 2011 15:59:56 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic1065439-60-1.aspxGood 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.Thu, 17 Feb 2011 09:32:03 GMTmatthew.greenslade-881391http://www.sqlservercentral.com/Forums/Topic1065439-60-1.aspx[quote][b]Dennis Wagner-347763 (2/17/2011)[/b][hr]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.[/quote]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.Thu, 17 Feb 2011 09:30:41 GMTdave.clarkhttp://www.sqlservercentral.com/Forums/Topic1065439-60-1.aspxPersonally 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.Thu, 17 Feb 2011 07:13:52 GMTroger.plowmanhttp://www.sqlservercentral.com/Forums/Topic1065439-60-1.aspxI'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! :-)Thu, 17 Feb 2011 07:05:16 GMTACinKChttp://www.sqlservercentral.com/Forums/Topic1065439-60-1.aspxDavid, 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.Thu, 17 Feb 2011 06:46:42 GMTDennis Wagner-347763http://www.sqlservercentral.com/Forums/Topic1065439-60-1.aspxVery useful article. Thanks very much for writing it.Thu, 17 Feb 2011 05:13:43 GMTKiarahttp://www.sqlservercentral.com/Forums/Topic1065439-60-1.aspxDavid,This is exactly how I do it!Nice article.JamesThu, 17 Feb 2011 02:20:42 GMTJames A Skipwithhttp://www.sqlservercentral.com/Forums/Topic1065439-60-1.aspxDocumenting the database is very important.You can see descriptions next to the table columns quite easily with XDetails plugin (free): [url=http://www.sqlxdetails.com]www.sqlxdetails.com[/url][img]http://www.sqlxdetails.com/uploads/2/8/2/6/2826684/5986945_orig.png[/img]Thu, 17 Feb 2011 01:48:03 GMTVedran Kesegichttp://www.sqlservercentral.com/Forums/Topic1065439-60-1.aspxGreat article David. Keep up the Good Work going. Many Thanks for sharing with us.Cheers,SatnamThu, 17 Feb 2011 00:22:49 GMTSatnam Singhhttp://www.sqlservercentral.com/Forums/Topic1065439-60-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Documentation/72473/">Oiling the gears for the data dictionary</A>[/B]Wed, 16 Feb 2011 22:53:39 GMTDavid.Poole