Documenting a giant web of inter-related stored procedures in an MS SQL database: What tool or format? [duplicate]

Question

I hope this is a question with a shorter answer than "Read a 1000 page book", but then, if that's the real situation, then hit me with it.

I am not a real DBA, I'm a software developer who is realizing we need a DBA, and yet the shop I work in has zero DBAs. However our MS SQL database design including several core stored procedures, is a giant mess. The stored procedures are slow, we suspect they have bugs, but we don't even know how they are expected to work, so we don't know how to fix them.

As a start I've decided we'll document how it's supposed to all work, then we'll start unit testing, and building up a set of unit tests that help prove that the stored procedures actually do work. The logic that they perform is a key part of our application, you could say, it's the "crown jewels" of our company's main product, and the way it works is completely undocumented.

I'm looking for the specific technical documentation that a professional DBA might expect to have existing, or might write themselves, if they had to, to understand a giant web of stored procedures that call each other.

1. What is the usual format for documenting a large stored procedure? Description of expected values for each In Parameter (ie "preconditions", "postconditions", ie, for boolean parameters what changes when you turn it on or off, etc?)

2. How does one usually document it? SQL comments only? External tooling that is specific to the purpose? External "documentation"? We have no SQL tools, other than MS SQL Management studio, but we are wondering if there is a tool that would make understanding, documenting, and testing our environment better. Maybe that is a better way to ask my question; What tool do I need to solve our mess?

Our goal is to be able to:

A. Use the documentation we generate, or whatever tools we add to our environment, to help understand how the procedures are supposed to work, so we can then go on to create unit test coverage for the stored procedures.

B. Show the client-app developers how to properly call each of these complex stored procedures.

C. Unit test our stored procedures.

Answer 1

The most important thing about documentation is that it makes sense to you. There's no really standard way of doing this.

If you've got lots of stored procedures that all connect to each other starting with a Visio diagram with one object for each procedure, then links between them so you can track how things go from procedure to procedure is probably a pretty good start.

Answer 2

The RedGate SQL Dependency Tracker tool might be helpful. It can show you graphically which database objects (SP's/views/tables) depend on each other. I've used it while working with some tables I was unfamiliar with to determine the order in which to disable constraints.

I also ran it on the whole database just for fun and it was way TMI. If you are able to focus it to specific areas of the DB that are not crazy-interdependent, it may be helpful. The dependency tree has options to visually organize itself using different algorithms and that alone makes an eval worthwhile.

Tracing. Another option is to write log lines at the start and end of critical stored procedures. Each row can include a date, a "detail level", a best-guess "context", "subcontext", proc name. and rowcounts. It will probably be a mess (think windows event log) but perhaps useful in some sections. If an SP is used to actually do the log insert and then it can be turned on/off easily without much additional load (ymmv).

Side note, I once loaded the printer with the cool 11 x 17 paper, found a nice tiny font and some logical indentation to summarize a complex flow of data/SP's into ~5 pages of some pseudo-SQL. I'm pretty sure I only ended up referring to it a few times and no one else dared go near it since there it was not standard and its hard to trust something not integrated and can get out of date. The documentation process did force a familiarity with the code though!