Programmatic detection of configuration options

A quickie… this morning I've been writing a bunch of T-SQL code (yes, occasionally I'm allowed to do that :-) and I needed to programmatically check whether backup compression is enabled on a SQL Server 2008 instance. I futzed about for a few minutes with sp_configure and I was starting to think of a temp table plus INSERT/EXEC when I thought there has to be a better way.

I turned to my trusted friend Books Online (I'm constantly amazed at some of the questions people ask in forums and on Twitter than can easily be answered by looking in Books Online…) to help out and of course was reminded straight away of the companion view to sp_configure: sys.configurations (see Books Online here).

Here's a quick example:

IF (SELECT [value_in_use] FROM sys.configurations
WHERE [name] = 'backup compression default') = 1
SET @BackupCompress = 1;

It's always amazing how much you forget and how much there is to know – enjoy!

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.