June 8, 2011 at 7:24 am
Hi everyone,
We're developing a new application which will eventually replace the core app at our company, and given the current database is rather horrid with no documentation I'd like to make sure this new database model is documented as best as possible not only for me and other developers but also for new developers down the road.
With this I'm curious to know how others do it, and are there any best practices for documenting data flow through a database model? I'm using Visio to model the database graphically (which I'm finding is less than ideal), but I need something that'll let me assign notes and business logic to each field in the database along with all database objects which use the field.
For now I'm working on a SQL script that'll identify all Procedures, Views, and Functions used by each field in the database along with another process to update the Extended Properties to hold any Notes and Business Logic for each database object. Once all this is updated I can hopefully create a report in SSRS to pull it all together and generate some documentation, but are there any applications or tools that'll do this for me or help in keeping all this sorted out? Given the application is still under development and in flux I need something that can be refreshed easily. I have an SSIS script that exports the Extended Properties to Excel so I can review them there, then it'll import the extended properties back into a refreshed database afterwards, but again this just seems like a long route to take if there are simpler ways of doing it.
Thanks for any suggestions, tips, ideas, etc on making this a simpler task.
June 8, 2011 at 7:52 am
Take a look at RedGate's SQL Doc tool. I've used that for this kind of thing, and it works quite well.
You can add extended properties to database objects pretty easily to document specific details about them, including business rules.
- 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
June 8, 2011 at 8:57 am
Another vote for Red Gate SQL Doc. Excellent tool, should do what you need.
Disclosure: I work for Red Gate.
"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 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply