{"id":658,"date":"2010-10-06T09:56:00","date_gmt":"2010-10-06T09:56:00","guid":{"rendered":"\/blogs\/paul\/post\/Programmatic-detection-of-configuration-options.aspx"},"modified":"2017-04-13T09:51:31","modified_gmt":"2017-04-13T16:51:31","slug":"programmatic-detection-of-configuration-options","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/programmatic-detection-of-configuration-options\/","title":{"rendered":"Programmatic detection of configuration options"},"content":{"rendered":"

\nA 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<\/font> when I thought there has to be a better way.<\/font>\n<\/p>\n

\nI 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 <\/font>here<\/font><\/a>).<\/font>\n<\/p>\n

\nHere's a quick example:<\/font>\n<\/p>\n

\n\t<\/p>\n

\n\tIF <\/font><\/font>(<\/font><\/font>SELECT<\/font><\/font> [value_in_use] FROM<\/font><\/font> sys<\/font><\/font>.<\/font><\/font>configurations
\n\t    <\/font><\/font><\/font><\/font>WHERE<\/font><\/font> [name] =<\/font><\/font> 'backup compression default'<\/font><\/font>)<\/font><\/font> =<\/font><\/font><\/font><\/font> 1
\n\t<\/font>SET<\/font><\/font> @BackupCompress =<\/font><\/font> 1;
\n\t<\/font><\/font><\/font><\/font>GO<\/font><\/font><\/font>\n\t<\/p>\n

\t<\/font><\/font><\/font><\/font><\/font><\/font><\/font>\n<\/p><\/blockquote>\n

<\/font><\/font><\/font><\/font><\/font><\/p>\n

\nIt's always amazing how much you forget and how much there is to know – enjoy!<\/font>\n<\/p>\n

\n<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[44],"tags":[],"class_list":["post-658","post","type-post","status-publish","format-standard","hentry","category-general"],"yoast_head":"\nProgrammatic detection of configuration options - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/programmatic-detection-of-configuration-options\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Programmatic detection of configuration options - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/programmatic-detection-of-configuration-options\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2010-10-06T09:56:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:51:31+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/programmatic-detection-of-configuration-options\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/programmatic-detection-of-configuration-options\/\",\"name\":\"Programmatic detection of configuration options - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2010-10-06T09:56:00+00:00\",\"dateModified\":\"2017-04-13T16:51:31+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/programmatic-detection-of-configuration-options\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/programmatic-detection-of-configuration-options\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/programmatic-detection-of-configuration-options\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Programmatic detection of configuration options\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Programmatic detection of configuration options - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/programmatic-detection-of-configuration-options\/","og_locale":"en_US","og_type":"article","og_title":"Programmatic detection of configuration options - Paul S. Randal","og_description":"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 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/programmatic-detection-of-configuration-options\/","og_site_name":"Paul S. Randal","article_published_time":"2010-10-06T09:56:00+00:00","article_modified_time":"2017-04-13T16:51:31+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/programmatic-detection-of-configuration-options\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/programmatic-detection-of-configuration-options\/","name":"Programmatic detection of configuration options - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2010-10-06T09:56:00+00:00","dateModified":"2017-04-13T16:51:31+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/programmatic-detection-of-configuration-options\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/programmatic-detection-of-configuration-options\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/programmatic-detection-of-configuration-options\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Programmatic detection of configuration options"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/658","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=658"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/658\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=658"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=658"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=658"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}