Voice of the DBA

Tag: syndicated

Finding Object Dependencies in SQL Doc
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.

PDF

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.
1. Ensure that this is regenerated automatically with every deployment. Either to QA or prod, your choice. Depends on how your developers use docs.
2. 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.
November 1, 2023
Adding a Foreign Key in the CREATE TABLE statement–#SQLNewBlogger
I had someone ask this question recently and had to double check the syntax myself, so I thought this would make a nice SQL New Blogger post.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Defining a Foreign Key

Most people define a foreign key like this:
```
ALTER TABLE [dbo].[OrderLine]  WITH CHECK ADD  CONSTRAINT [FK_OrderLine_Order] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Order] ([OrderID])
GO
```
This assumes I’ve added a table called dbo.Order with a PK of OrderID.

However, I can do this in the CREATE TABLE statement, like shown below. I add a new section after a column with the CONSTRAINT keyword. Then I name the constraint, which is always a good practice. I can then add the FK keyword, the column and the references that connects this child column to the parent column.
```
CREATE TABLE dbo.OrderLine
( OrderLineID INT NOT NULL CONSTRAINT OrderLinePK PRIMARY KEY
, OrderID INT
, Qty INT
, Price NUMERIC(10,2)
, CONSTRAINT FK_OrderLine_Order FOREIGN KEY (OrderID) REFERENCES dbo.[Order](OrderID)
)
GO
```
Easy to do and this keeps my code clean.

Note that if I script this out in SSMS, I’ll get this:
```
CREATE TABLE [dbo].[OrderLine](
[OrderLineID] [int] NOT NULL,
[OrderID] [int] NULL,
[Qty] [int] NULL,
[Price] [numeric](10, 2) NULL,
CONSTRAINT [OrderLinePK] PRIMARY KEY CLUSTERED
(
[OrderLineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[OrderLine]  WITH CHECK ADD  CONSTRAINT [FK_OrderLine_Order] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Order] ([OrderID])
GO
```
Nothing wrong with that, but knowing both syntaxes is a good idea. Plus, if you know this is a child column, define it right away.

SQL New Blogger

This is a post that took me about 15 minutes to write. I had to create and drop the tables a few times and verify I had the syntax correct, and then explain and format things.

This is a core skill for a DBA or developer. You ought to know how to define a FK and use them where appropriate. Write your own post to show how to build a FK for some scenario that you work with in your job, or in a project.
October 30, 2023
Suppressing the FOMO

I saw a post recently on Twitter/X/whatever we call it. It was for a DevOps in a Day from Redgate taking place in Jacksonville.

I’ve been a part of the tour this fall, and when we announced the Jacksonville date, Jeff reached out and asked if we could get together. I told him I wasn’t coming, and he asked why not. At the time I said I was busy and wanted the week off, which was true. I’m not sure Jeff felt that made sense, but Grant was going, so everything was fine.

However

When I saw the post, I felt like I should be there and I was missing out. I slightly regretted not going. After all, the week this event took place, I was at home, with no travel.

That was my first M-F week at home since July 10-14. I was trying to protect it, and I think I did the right thing by not going and letting Grant handle things, but I still felt bad. I had some FOMO (Fear of missing out).

FOMO

I find FOMO drives a lot of people to do extra things. They do more work, they go places, they get caught up with friends/coworkers/family/etc. because they want to participate in something that’s fun or valuable or helpful to their career.

I get it. I feel the same way, but I have learned often to step back from situations. This year, I knew the fall would be tough with travel, but I was also excited and looking forward to the tour. I wanted it to go well and for Redgate to do more in 2024.

However.

I don’t need to do it all. Grant can handle the day, and if not, Ryan can help. We’ve already talked about the need to plan further in advance and split the load. There is plenty of work.

We’re Not Saving Babies

My wife has used this phrase a lot. Most of us aren’t saving babies. If you are, then work hard, heck work 100 hours a week and then take time off, but save babies.

Most of us do work that could wait until tomorrow. We can let someone else do some of the work. Even if someone does a better job, often there is so much work that they company will still need you to do something.

Certainly Redgate will still use me at some events, and I’ll still travel.

I just don’t need to do all the events.

I keep telling myself that I’m certainly not saving babies, and there is plenty of other work. And, I’ve been to Jacksonville (and many other places) this year. I can skip a few trips.

Side Note

Even as I had FOMO, I had just sent an email to the US offices thanking them for an invitation to the Christmas parties, but declining to go since I didn’t want to get on another airplane this year. I still have two trips after this week, and I’m limiting my life to those.

October 27, 2023
A New Word: Funkenzwangsborstellung

funkenzwangsborstellung – n. the primal trance of watching a campfire in the dark.

I am not a big camper, but 3-4 times a year we usually go out with horses. Usually my wife rides and I relax, play guitar, and cook. Often we do build a campfire if the area isn’t under restrictions, and it’s nice to sit and stare into the fire.

I don’t know what it’s relaxing, but I do enjoy the funkenzwangborstellung.

From the Dictionary of Obscure Sorrows

October 27, 2023