Introduction

Documentation for the tasks related to a particular project is required for easy maintenance and quick reference. Number of situations has been encountered where we all go through user-manuals or read-me files for getting help/solutions. Most of the applications are having documents that provide in-depth description of the design, code and maintenance related stuff. Most of these documents are for the development team but some of them are also provided to the customers.

Consider a scenario:

You are currently involved in designing a database i.e., created tables, defined constraints, written various scripts, jobs, etc. One fine day, you got an opportunity to work with Microsoft (Nice opportunity, isnt it!!!). The company for which you are working has already hired your replacement. Doing transition or knowledge transfer becomes tuff and boring. So, you try to get rid it as fast as possible and in turn making the life of your REPLACEMENT miserable.

There will be documents written when the database design was discussed and some of them include the database design in detail. But, some way or the other we miss out on small things that are very crucial, specially the updates or modifications to the database objects. Also, the documents that are created are not concentrated on the complete database design. For example:

• Why a particular table is created?

• What is the need of a particular column?

• What is the use of XYZ stored procedures?

• What is done when is a particular job executed?

These are some mystery questions that will be un-answered. Unless the REPLACEMENT takes the initiative and start digging in the database, the mystery would not be solved.

How to Document?

There are various tools available that can be used to document your database. The best is Microsoft Word. Instead of writing the overview of the database and the storing the sql-scripts in the documents, it is a good practice to write down the details about each tables, columns, stored procedures, views, jobs and other database objects. Still, this method demands regular update of the documents as and when the design or functionality changes are made.

Extended properties can also be used to document individual database objects. There are various stored procedures which allows to add and modify details.

Using SQL Server 2005 as your database will be useful, as it provides a good way to add description to all the database objects when they are created. You can specify description for each column [also in SQL Server 2000]. In SQL Server 2005, it is also possible to add description for tables, stored procedures and other database objects. It is helpful because you can get the information instantly without searching a document.

There are tools available that will run through your database and give output as complied-html file or normal html pages. The output is well categorized and contains all the database objects, with their descriptions. I have tried ApexSQL Doc, and it is really helpful. It provides a good user-interface for selectively documenting various database objects.

Conclusion

Documenting the database perfectly up to the column level is necessary and a good practice so that the design can be better understood. Also, there are some instances when the designer of the database is not sure about the existence of a particular table/column/script. Putting little extra effort by specifying descriptions for the database objects will make life easier for everyone.