G’day,

I was checking out some of our configuration options today using sys.configurations and I started to wonder how many people actually use this table, or whether most people simply use the more common system stored procedure sp_configure.

As sys.configurations is simply a table, we can use an ORDER BY clause as in

--SELECT FROM THE TABLE
SELECT * FROM sys.configurations C
ORDER BY C.name;

Which lists the configuration options in exactly the same order as the following call to sp_configure.(depending on the value of “show advanced options” the amount of options returned may vary.)

/*Just run the
Stored proc.*/EXEC sp_configure

However, obviously with SELECT statement we can also use a WHERE clause (or any other valid syntax)

SELECT * FROM sys.configurations C
WHERE C.is_advanced = 1
ORDER BY C.is_dynamic , C.name;

Of course it’s probably a matter of personal choice whether to write a query or just go for the system stored procedure. But one of the reasons that I like the query option is that I can see the advanced options straight away – even if I have “show advanced options” set to 0. If I use the stored procedure then I have to first set “show advanced options” to 1, then run a RECONFIGURE (as “show advanced options” is a dynamic option). Next the system stored procedure is run and finally “show advanced options” is set back to 0, followed by another RECONFIGURE. Note also that the code below ends by setting “show advanced options” to 0, when it may have already been at 1 in the first place.

so the full TSQL actually looks like

EXEC sp_configure 'show advanced options' , 1;
RECONFIGURE;
EXEC sp_configure;
EXEC sp_configure 'show advanced options' , 0;
RECONFIGURE;

instead of

SELECT * FROM sys.configurations C
ORDER BY
C.name;

which seems like less typing and also simply just leaves any setting for “show advanced options” alone.

But there is another good use for the table option, rather than the stored procedure. And that is to pull all of your setting out and back them up. Maybe pushing them to a text file on the server using Powershell or SSIS.

You can format the query anyway you like (perhaps in the form of runnable SQL statements) and then in a DR situation you have a full list of all your configuration setting without having to restore master to get them.

anyway, have a nice day.

cheers

Martin.