I have a master post which links to a set of powershell scripts all related to this. The master which links to all the scripts is here.

While exploring some SQL blogs, I ran across an interesting script from Brad McGehee at Simple-Talk.com. The original source is here.
What I wanted to do was modify it to use a flat file text list of servers, rather than DSN’s. I have over 30 production instances in flat domain with an external domain that trusts the internal domain, so I can use SSPI to connect to each.

Here’s my version using ADO.NET connections. Since I have only a single remaining SQL 2000 instance, and only a single 2005 instance, I took out the logic of the query version, focusing only on the SQL 2008 query without worrying about the Compression as I do not use that at all. I also added some additional information that I thought was relevant such as Recovery model desc, compatibility level, collation, and a couple others.

#change these settings to your environment
$Filename='DatabaseProperties'
#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 AS 'Database_Name' ,
        user_access_desc AS 'Restrict Access' ,
		recovery_model_desc AS 'Recovery Model',
		compatibility_level AS 'Compatibility Level',
        page_verify_option_desc AS 'Page Verify' ,
        snapshot_isolation_state AS 'Allow Snapshot Isolation' ,
        cast(is_ansi_null_default_on as tinyint) AS 'ANSI NULL Default' ,
        cast(is_ansi_nulls_on as tinyint) AS 'ANSI NULLS Enabled' ,
        cast(is_ansi_padding_on as tinyint) AS 'ANSI Paddings Enabled' ,
        cast(is_ansi_warnings_on as tinyint) AS 'ANSI Warnings Enabled' ,
        cast(is_arithabort_on as tinyint) AS 'Arithmetic Abort Enabled' ,
        cast(is_auto_close_on as tinyint) AS 'Auto CLOSE' ,
        cast(is_auto_create_stats_on as tinyint) AS 'Auto Create Statistics' ,
        cast(is_auto_shrink_on as tinyint) AS 'Auto Shrink' ,
        cast(is_auto_update_stats_async_on as tinyint) AS 'Auto Update Statistics Asynchronously' ,
        cast(is_auto_update_stats_on as tinyint) AS 'Auto Update Statistics' ,
        cast(is_cursor_close_on_commit_on as tinyint) AS 'Close Cursor on Commit Enabled' ,
        cast(is_concat_null_yields_null_on as tinyint) AS 'Concatenate Null Yields Null' ,
        cast(is_db_chaining_on as tinyint) AS 'Cross-Database Ownership Chaining Enabled' ,
        cast(is_date_correlation_on as tinyint) AS 'Data Correlation Optimization Enabled' ,
        cast(is_read_only as tinyint) AS 'Database Read-Only' ,
        cast(is_local_cursor_default as tinyint) AS 'Default Cursor' ,
        cast(0 as tinyint) AS 'Encryption Enabled' , --not in 2005
        cast(is_arithabort_on as tinyint) AS 'Numeric Round-Abort' ,
        cast(is_parameterization_forced as tinyint) AS 'Parameterization' ,
        cast(is_quoted_identifier_on as tinyint) AS 'Quoted Identifiers Enabled' ,
        cast(is_read_committed_snapshot_on as tinyint) AS 'Read Committed Snapshot' ,
        cast(is_recursive_triggers_on as tinyint) AS 'Recursive Triggers Enabled' ,
        cast(is_broker_enabled as tinyint) AS 'Service Broker Enabled' ,
        cast(is_trustworthy_on as tinyint) AS 'Trustworthy',
        cast(is_published as tinyint) AS 'Published',
        cast(is_subscribed as tinyint) AS 'Subscribed',
        cast(is_merge_published as tinyint) AS 'Merge Publish',
        cast(is_distributor as tinyint) AS 'Distributor',
        collation_name AS 'Collation Name',
        log_reuse_wait_desc
FROM    sys.databases ;
"@

# 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 #keep a tally
	#if  ($server.version -eq 2000) {$SQL=$SQL2000} #get the right SQL Script
	#if  ($server.version -eq 2005) {$SQL=$SQL2005} 
	#if  ($server.version -eq 2008) {$SQL=$SQL2008}
	$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:AH").NumberFormat = "[Red][=0]û;[Blue][=1]ü"
		$excel.Columns.Item("F:AH").Font.Name = "Wingdings" 
		$excel.Columns.Item("F:AH").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
        [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()

Here’s a screen shot of the output from one of our Sharepoint database servers.