March 12, 2010 at 11:37 am
Comments posted to this topic are about the item Database Documentation: Joining up the Dots
Best wishes,
Phil Factor
March 13, 2010 at 2:07 pm
I have been documenting our database platform for nearly 9 years. We use structured headers for stored procedures and views (including summary, parameters, blocks, remarks and about a dozen others), and my tool Document X! parses those objects and creates an HTML documentation project. I then import that into a RoboHelp 8 project and create a searchable, indexed Web Help project that I publish on the company intranet. Document X! does a great job pulling out the logical relationships in the databases, including all dependencies (in both directions), and the scripts and schemas. I get the stored procedures, views, tables, and any other object I might want to include in the technical documentation (defaults, user defined functions, user defined data types, full text catalogs). I can even get permissions if necessary. The value of publishing it as a RoboHelp Web site is that all of the objects are fully searchable so that you can find things fast. The other value: the entire software development and database administration team has a structure and methodology to hang their documentation on so that standards prevail within the team as a whole. In the early years of the project, I spent a lot time in the code itself writing comments. In the last few years, only developers write the comments to their code. Document X! even picks up the version comments generated automatically by the team's version control tool, so that the name of the developer, the date, the version, and a version comment is captured and published to the documentation web site. One place for all comments. I have the option as well to publish the full text of the stored procedures, or just the comments and headers. My site includes everything.
March 13, 2010 at 6:25 pm
Phil,
In my mere 15 years I rarely if ever receive a request to document a database. That covers hundreds of them over hundreds of projects. In my programming days it was "get it done asap". As an admin it's "fix it asap". As a lead it's "fix it asap and who do I blame?"
Modern documentation in my experience is the purvue of the Indian outsourcers looking for a way to duplicate our work in cookie-cutter fashion. And their documentation never reflects what really goes on.
It's entirely possible, even likely, that I have no idea what you are talking about. If that's the case, it doesn't say much for the documentation quality of your editorial.
That leads to my main point. Documentation explains simply and clearly what an object, function or property does. It provides working examples. Intellisense is not documentation. It is Microsoft's attempt at reading minds.
March 13, 2010 at 8:52 pm
I am using Extended properties for ages. But I agree with Phil - vendor should provide better Documenting facilities,
rather than force us to "re-invent the wheel" in DIY solutions!
March 13, 2010 at 11:26 pm
I use the tool BIDocumenter, from Pragmatic Works (http://www.pragmaticworks.com)
Disclaimer : we signed up as a reseller locally.
But the reason we did is that it is an excellent tool, documenting the DB, SSIS packages, SSAS cubes, and SSRS
And also has data lineage - showing where data came from
March 14, 2010 at 4:28 am
Nice tool - thanks for the tip!
March 14, 2010 at 7:41 am
I'm using extended properties. Just by filling them in the SSMS Database Diagram but it's obviosly pretty inconvenient cause it does not cover documentation of SPs/FNs etc.
March 14, 2010 at 11:06 am
I've usually only documented databases at a point in time, when there is this need to a large scale change to the system, or a lot of new developers start working with things. Otherwise is seems that we almost require someone to build their own knowledge as they go along as a way of understanding the system.
If there was some other benefit, and we could easily access documentation, I think it might be more valuable. Especially if we could easily expose things like Extended Properties in places like SSRS where a user could better understand what they are working with. It would be great if a view read back through to the EP of the source table as well and exposed the data there.
March 14, 2010 at 5:17 pm
You could use DDL Trigger - every time when SP/UDF had been modified, it will retrieves list of input/output parameters, build an XML document and store it with designated key in Extended Properties.
BTW you even could snap and record definition of recordset(s) returned by SP/UDF (with SQL CLR code).
March 15, 2010 at 4:20 am
Has anyone documented a database that was for a Medical Device / Phara environment application and how did you achieve it? Tks.
March 15, 2010 at 4:48 am
In an ideal world (still looking for it) you would design all your databases from a model, using a good tool, my preference Power Designer (Sybase). Then apply all changes to the model first, so you would always have an accurate model of your database and you could always see what would be impacted when changing your database. The model would end up generating the documentation and data dictionary.
Unfortunately it never seems to work that way anymore, business want change to happen far to quickly, so they just rush into the development not taking the time and effort to put down the ground work in the design stage. They always end up paying the price later, but then that comes out of a different budget.
March 15, 2010 at 5:21 am
In a real world, Carolyne you always challenged with a task to clean somebody else mess, and I was VERY VERY lucky
when I got couple of weeks to understand what exactly my team inherited in terms of data store
March 15, 2010 at 5:26 am
Duplicate
March 15, 2010 at 6:33 am
I try to do at least some basic documentation for my DBs. I put a description on all non-intuitive fields, and I use structured headers for all stored procedures and functions (including summary, author, date created, change history, etc.). It would be nice to have this information available in a popup when hovering and it would be nice to have that info automatically included in documentation output.
For example, we use the SQLDOC tool from RedGate. It a nice utility and it includes the field descriptions in the documentation output, but it would be nice to have the stored procedure / function summaries and parameters descriptions included as well. Having a documentation tool built in to the software instead of having to buy a third party tool would be nice as well.
March 15, 2010 at 6:35 am
I think it would be useful to have a Comments "parameter" for the various create and alter DDL commands. If it could handle XML, that would be great. The data thus created should be accessible through sys.all_objects, sys.tables, et al. If the XML scheme for this had a default, but could be replaced/modified/enhanced as required, that would be even better.
Beyond that, trapping DDL commands and logging them via a trigger, then having an XML Comments column in the logging table, is a viable but much less valuable alternative. Much too prone to "I forgot to update it". Could set up a job to run at the end of the night and send an e-mail to the DBA and the dev if something in the log still doesn't have a comment by then.
I haven't implemented such a system, but it would probably work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply