I had a customer recently ask if they could find out which objects are dependent on others using SQL Doc. This post shows how to do that.
Using SQL Doc
In the SQL Doc application, you can dive down into the various objects in your database. As I’ve shown below, I navigated on the left side down to a specific object.
This gives me the basics of this object, but I can scroll down and see more. The lower part below the script shows what this object depends on (Uses) and what other objects depend this one (Used By). In this case, this object depends on dbo.ErrorLog and dbo.uspPrintError.
The reverse of this can be seen in these objects. If I look at dbo.ErrorLog, then I see this proc (dbo.uspLogError) listed.
This is really a preview of documentation, not the docs themselves. While you can edit the MS_Description at the top, this isn’t the tool I’d expect most users to use. Instead, you ought to click Generate documentation at the top and choose the type you need.
Or better yet, use the command line and ensure this is regenerated after every release.
Searching the Documentation
Once the documentation is generated, it’s in static text files. These might be Word or some other format, but they are files in your machine.
If you want to search something, then you would use that search facility. A few examples below:
Word
In Word, as you can see, if I hit CTRL+F, I can search for something, like Error. That gives me a lot of results.
Not the easiest thing to search, but seeing this results in the TOC let’s me scroll down to jump to a page.
If I generate a PDF, this often renders in some application. For example, by default, this opens in a browser for me. If I want to search for “error”, I can again, CTRL+F and enter the term.
HTML
The HTML docs open in a browser, but they’re not directly searchable. There isn’t a place to click search.
If I use CTRL+F, it doesn’t work because the docs for each object are in separate files.
However, I can do some searching in the files if I use something like Visual Studio code to open the entire folder. It’s not the prettiest search, but it works.
Recommendations
I’d recommend you do two things.
- Ensure that this is regenerated automatically with every deployment. Either to QA or prod, your choice. Depends on how your developers use docs.
- Pic a searchable format, either Word or PDF.
SQL Doc is one of those utilities that can help a team share information, especially to report writers or others that might need to understand what fields are available and what they are used for, but don’t know the databases that well.
One thing to think about is a layer of abstraction for non technical people where you only generate docs for views that have well named columns for people to use in reports.