TomThomson (12/29/2015)

---

SQLRNNR (12/29/2015)

---

TomThomson (12/29/2015)

---

Quite a nice question, but why involve PowerShell?

I was wondering that myself. It is easy enough to get a list of the configs from a simple select statement, unless the SMO is revealing a setting that the sql statement would not.

Well, SP_CONFIGURE (without parameters) will display only the settings that don't require 'show advanced options' to be 1; so in SQL server 14 it will show only 18 of the 70 options unless show advanced options is on. The SMO object contains all the options, and Steve's SMO shows the whole object.

In addition, the SMO object has 9 attributes per option intead of just 5; the 4 missing from SP_CONFIGURE output are the Option Number (these are not a dense set, but I've found them useful because they are not as big as the names), whether the option is advanced or not, whether the option requires an instance restart to change or not, and a plain English explanation of what the option is (maybe useful when the display name is a bit opaque, but I've never had a use for it). I've never bothered to find out how to get these additional columns in SQL because it wasnt in SQL that I needed them - but I can see it being a simple select statement as you suggest.