Powershell script to gather database level properties into Excel file

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.

3 thoughts on “Powershell script to gather database level properties into Excel file

  1. Pingback: Extended Properties as a Run Book? « DBA Star

  2. You’re so awesome! I do not think I’ve truly read through something
    like this before. So good to discover someone with a few genuine thoughts on this subject.

    Really.. thank you for starting this up. This website is something that is
    needed on the web, someone with some originality!

  3. Howdy would you mind stating which blog platform you’re working with?
    I’m planning to start my own blog in the near future but
    I’m having a tough time selecting between BlogEngine/Wordpress/B2evolution and Drupal.
    The reason I ask is because your design seems different then most blogs and I’m looking for something completely unique.
    P.S My apologies for getting off-topic but I had to ask!

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>