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

Page 1 of 2 | Next page