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.
Pingback: Extended Properties as a Run Book? « DBA Star
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!