database best documentation

gudii9 used Ask the Experts™
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
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
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.
Doug BishopDatabase Developer

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial