The DBAdmin Toolkit

It’s time to put it all together into something useful, but before we do, we need to decide where to put it. There are two schools of thought on the subject: One maintains that user objects should never go into the master database, while the other maintains that administrative tools aren’t exactly “user” objects and so are fair game. I’m somewhat torn between the two positions so I’ve split the difference. I do put my toolkit routines in master, but as part of a dedicated schema named DBAdmin. That makes them quickly identifiable and easily removable without having an admin database hanging around drawing attention to itself. Here’s how I set up the schema:

The CREATE SCHEMA command is invoked with an EXECUTE statement because it needs to be the first statement in a batch and I need it to be within a conditional expression. The second batch builds and executes dynamic T-SQL that identifies DBAdmin schema objects and drops them. I have both batches at the top of my creation script so that I don’t have to worry about CREATE vs ALTER later on.

In my previous post, I retrieved some registry values that look like they’d be very useful in other routines: Default Data, Default Log, Backup Directory, and others. I’ll create a scalar-valued function wrapper for xp_instance_regread to make it easier to use:

Finally, I package everything up in a stored procedure named GetServerInfo, which starts off declaring a table variable to hold the results until I return them al at once at the end:

To put this information literally at my fingertips, I assigned the EXECUTE master.DBAdmin.GetServerInfo; statement to a keyboard shortcut in SSMS. I used Ctrl+F1 because the routine is kind of help-ish but if you don’t like that one there are ten others to choose from.

Using these tools is sort of like buying unfinished furniture—they’re complete and they work but they lack polish. As with unfinished furniture, the idea here is for you to add whatever final touches are necessary to make them work for you. I’m interested in seeing whatever enhancements you want to share in the comments.

Leave a Reply

Your email address will not be published.