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.
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?)
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.