The Basics - How to Document a Script

An occasional series looking at best practice for common documentation tasks and situations.

You work on a project that involves making database changes using SQL scripts.  Your customers' DBAs need to know what the scripts will do before they run them.  This article covers what you typically need to include in your script documentation.

Before documenting any scripts, make sure you include a warning to your customers to back up their database before running any scripts.  Ideally, customers should test the scripts on a test version before applying them to live.


1 - What Database type and version is the script designed for?

Does the script update MSSQL? Oracle? MongoDB? Something else?  What versions can the script be run on? Are there scripts that do the same thing for different database types and versions?

For example:

This script is designed for Oracle 10g and later.  You can find the equivalent Mongo script here [where "here" is a hyperlink to the Mongo script location].


2 - Do you need any particular permission level or security settings to run the script?

Do you have to have a specific system or object privilege?  What user roles should run this script?  Is the script likely to impact high-security areas such as users, passwords, personal details, company accounts, and so on?

For example:

This script requires the SELECT ANY TABLE privilege.  We recommend that a SYSBDA review the script before running as it will add a constraint to the main USER table.


3 - Which database or schema does it need to be run against?

If your application(s) provide more than one database (separate finance and personnel databases, for example), make it clear which database the script needs to be run against. Provide a warning about running the script against any but the intended database.

For example:

This script applies to the Finance database/schema only.  Do not run it against any other databases as there may be unintended consequences up to and including data loss.


4 - What does the script do at high level?

Explain how the script amends the structure or the content of the database.  The primary types of change are:

• Add/delete/amend table(es)
• Add/delete/amend column(s)
• Add/delete/amend index(es)
• Add/delete/amend constraint(s)
• Add/delete/amend data

For example:

This script will insert 3 new columns to the User table and populate the existing rows with default data.


5 - What does the script do in detail?

Provide all available information about the detail of the script.  This will depend on the type of script, but for adding or amending tables or columns include the following:

• Friendly name
• SQL name
• Primary keys
• Foreign keys
• Nullable
• Data types
• Data lengths

Explain why something is being added/amended/deleted, and ideally link to the release notes for the code change that the database change is required for.  If data is being deleted explain why it isn't needed any more; flag up data deletion strongly so that customers can make an informed decision about running the script with full knowledge of what will happen when they do.

There are so many possible combinations that examples would be impractical here. Focus on putting the data into a readable format such as a table.  This will also help you make sure that the information provided is consistent.


6 - How will this affect reporting and/or database diagrams?

Provide an updated entity relationship diagram and/or data dictionary.  Explain that cusomters will need to amend any database reports - e.g. SRS, Oracle, Crystal, etc - and explain what they'll need to change.  This is especially important for columns changes; new tables won't affect existing reports and index/constraint changes will rarely have any impact either. If a table or column has been deleted and replaced with something else, explain the mapping of the data between the old and new structure.

For example:

The new data dictionary can be found on our website here [provide link].  If you have reports that reference the deleted column, please amend them to refer to [new tablename.columnname] instead. 


These should cover all of the generic bases for script documentation.  Create templates for the types of scripts you need to document, including any bespoke requirements that aren't covered here, and you're good to go!  If you think there's something missing from the list, drop me a comment below and I'll add it in.