This is part 2 of a multipart posting on some PowerShell scripts that I’ve found and made some modifications to. These scripts I have used to document my environment without having to manually type information in to the spreadsheets. The master which links to all the scripts is here.

This script gathers the information from sys.configurations. It also expects a flat file named Servers.txt with a list of SQL Servers to connect to. It will connect via SSPI, so make sure you run it with a Windows account with the appropriate permissions on each SQL Server.

#change these settings to your environment
$Filename='SQLServerProperties'
#these are actually User ODBC DSNs DSNs. Better for Excel.

$serverList = "Servers.txt"

# constants.
$xlCenter=-4108
$xlTop=-4160
$xlOpenXMLWorkbook=[int]51
# and we put the queries in here
$SQL=@"
SELECT name [Name], 
    value as [Configured Value], 
    value_in_use AS [Running Value], 
    minimum AS [MIN VALUE], 
    maximum AS [MAX VALUE], 
    CASE WHEN CAST(CAST(Value_in_Use as int) - CAST(value as int) as tinyint) = 0 THEN 1
	ELSE 0 
	END
	AS [RUNNING AS CONFIGURED],
    cast(is_dynamic as tinyint) AS [Dynamic], 
    cast(is_advanced as tinyint) AS [Advanced],
    description AS [Description],
    configuration_id
FROM    sys.configurations 
order by name;
"@


# before we do anything else, are we likely to be able to save the file?
# if the directory doesn't exist, then create it
#if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing
#  {
#  New-Item "$DirectoryToSaveTo" -type directory | out-null
#  }
$excel = New-Object -Com Excel.Application #open a new instance of Excel
$excel.Visible = $True #make it visible (for debugging more than anything)
$wb = $Excel.Workbooks.Add() #create a workbook
$currentWorksheet=1 #there are three open worksheets you can fill up
foreach ($server in (Get-Content $serverList)) 
	{ #only create the worksheet if necessary
	if ($currentWorksheet-lt 4) {$ws = $wb.Worksheets.Item($currentWorksheet)}
	else  {$ws = $wb.Worksheets.Add()} #add if it doesn't exist
	$currentWorksheet += 1 
	$currentName=$server.Replace("\","_") # and name the worksheet
	$ws.name=$currentName # so it appears in the tab 
	# note we create the query so that the user can run it to refresh it
    
    $myconnectionstring = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=master;Data Source=$server"  
    $adOpenStatic = 3 
    $adLockOptimistic = 3  
    
    $objConnection = New-Object -comobject ADODB.Connection 
    $objRecordset = New-Object -comobject ADODB.Recordset  
    
    $objRecordset.CursorLocation = 3
    
    $objConnection.Open($myconnectionstring) 
    $objRecordset.Open($SQL,$objConnection,$adOpenStatic,$adLockOptimistic)  
    
    $objRecordset.MoveFirst()
    
    $qt = $ws.QueryTables.Add($objRecordset,$ws.Range("A1"))
	# and execute it
	if ($qt.Refresh())#if the routine works OK
		{
	              $ws.Activate()
		$ws.Select()
		$excel.Rows.Item(1).HorizontalAlignment = $xlCenter
		$excel.Rows.Item(1).VerticalAlignment = $xlTop
		$excel.Rows.Item(1).Orientation = -90
		$excel.Columns.Item("F:H").NumberFormat = "[Red][=0]û;[Blue][=1]ü"
		$excel.Columns.Item("F:H").Font.Name = "Wingdings" 
		$excel.Columns.Item("F:H").Font.Size = 12
		$excel.Rows.Item("1:1").Font.Name = "Calibri"
		$excel.Rows.Item("1:1").Font.Size = 9
		$excel.Rows.Item("1:1").Font.Bold = $true
		$excel.Columns.Item(1).Font.Bold = $true
        $Excel.Columns.Item("A:I").Font.Size = 9
        [void] $Excel.Columns.AutoFit()
		}

	}
$filename=$filename -replace  '[\\\/\:\.]',' ' #remove characters that can cause problems
$filename = "$filename.xlsx" #save it according to its title
if (test-path $filename ) { rm $filename } #delete the file if it already exists
$wb.SaveAs($filename,  $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$wb.Saved = $True #flag it as being saved
$objRecordset.Close() 
$objConnection.Close() 
$wb.Close() #close the document
$Excel.Quit() #and the instance of Excel
$wb = $Null #set all variables that point to Excel objects to null
$ws = $Null #makes sure Excel deflates
$Excel=$Null #let the air out
# Hristo Deshev's Excel trick 'Pro Windows Powershell' p380
[GC]::Collect()