database best documentation

we have hundreds of flows like registration, sales, cancellation from front end java applicatio to SQL database.

Microsoft SQL database has hundreds of stored procedures, tables, columns, joins, primary keys, foreign keys etc.

it is getting hard to check database stored procedures, tables if issue come on say sales flow to check quickly as no proper documention on database side.

what kind of documentation is good to have on database side.

i think like one excel spreadsheet  with columns like stored procedures, then next column relevant tables then next column table 1 columns, table 2 columns and then highight primary key as yellow color and then next column joins and other related tables

please advise any other better ways and best practices and links resources on it
LVL 7
gudii9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
Having no documentation is very common.  I made a very good living as a freelancer coming on to clean up others' undocumented bad code.

For starters, I recommend creating a data dictionary, which is a doc that data DBA's or development people (AppDev?) will create that takes the database schema and adds business English-friendly description.  

The primary users of the tables and views will be anyone that connects to these tables and views such as AppDev groups and ETL builders, and of course internal SQL Server developers.  

The primary users of the SP's will be mostly internal IT developers, and occasionally AppDev groups where the SP feeds their front-end.  Also code comments inside SP's are extremely valuable to SQL developers to understand what the SP does, why it changed, etc.   I can't tell you how many times I looked at an undocumented stored procedure and wondered to myself 'What the hell is this, and what was the previous developer thinking??'

RedGate has a SQLDoc that allows for all of this.  There was also an HTML Help that created .chm or .hlp files, but I haven't worked with these in awhile.  Any of these tools will cost $$$, unless you want to go the cheap route and publish Excel spreadsheets.

Good luck.
Jim

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Doug BishopDatabase DeveloperCommented:
No points please, I just want to add to Jim's comments. I've used SQLDoc from Redgate and very highly recommend it. There is a 14-day free trial available that is 100% functional. It can create a Word or PDF document or a clickable .chm file that you can navigate through.

Along with his suggestion, I would also look into creating extended properties. They can be created on pretty much any object on any level (e.g. on the database, a table, table columns, indexes, stored procedures, functions, etc.). Most documenting utilities (like SQLDoc) will pick these up and include them in the documentation. They make for great self-documentation. It is a pain to do after the fact, but worth the effort if time can be devoted to it. In SSMS, right-click on the object, select Properties, then select the Extended Properties page.

Another good Redgate tool is their SQL Dependency Tracker, which allows you to map out dependencies within your database, You can print it out but I wouldn't recommend that unless you've got a relatively small database as it can get VERY crowded and you'll be unable to make heads or tails of the printout.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.