Database script

Post date: Nov 30, 2010 3:30:17 PM

The following script creates an excel sheet with the following values:

DATABASE NAME COLLATION COMPATIBILITY LEVEL AUTOSHRINK RECOVERY MODEL SIZE (MB) SPACE AVAILABLE (MB)

Make sure you've got a text-file with servernames and put it in C:\scripts\SQL\Servers.txt:

example:

SQL01

SQL02

SQL03

etc.

Code:

CLS

#Create a new Excel object using COM

$Excel = New-Object -ComObject Excel.Application

$Excel.visible = $True

$Excel = $Excel.Workbooks.Add()

$Sheet = $Excel.Worksheets.Item(1)

#Counter variable for rows

$intRow = 1

#Read thru the contents of the SQL_Servers.txt file

foreach ($instance in get-content "C:\Scripts\SQL\Servers.txt")

{

#Create column headers

$Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"

$Sheet.Cells.Item($intRow,2) = $instance

$Sheet.Cells.Item($intRow,1).Font.Bold = $True

$Sheet.Cells.Item($intRow,2).Font.Bold = $True

$intRow++

$Sheet.Cells.Item($intRow,1) = "DATABASE NAME"

$Sheet.Cells.Item($intRow,2) = "COLLATION"

$Sheet.Cells.Item($intRow,3) = "COMPATIBILITY LEVEL"

$Sheet.Cells.Item($intRow,4) = "AUTOSHRINK"

$Sheet.Cells.Item($intRow,5) = "RECOVERY MODEL"

$Sheet.Cells.Item($intRow,6) = "SIZE (MB)"

$Sheet.Cells.Item($intRow,7) = "SPACE AVAILABLE (MB)"

#Format the column headers

for ($col = 1; $col –le 7; $col++)

{

$Sheet.Cells.Item($intRow,$col).Font.Bold = $True

$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48

$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34

}

$intRow++

#######################################################

#This script gets SQL Server database information using PowerShell

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

# Create an SMO connection to the instance

$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

$dbs = $s.Databases

#Formatting using Excel

ForEach ($db in $dbs)

{

#Divide the value of SpaceAvailable by 1KB

$dbSpaceAvailable = $db.SpaceAvailable/1KB

#Format the results to a number with three decimal places

$dbSpaceAvailable = "{0:N3}" -f $dbSpaceAvailable

$Sheet.Cells.Item($intRow, 1) = $db.Name

$Sheet.Cells.Item($intRow, 2) = $db.Collation

$Sheet.Cells.Item($intRow, 3) = $db.CompatibilityLevel

#Change the background color of the Cell depending on the AutoShrink property value

if ($db.AutoShrink -eq "True")

{

$fgColor = 3

}

else

{

$fgColor = 0

}

$Sheet.Cells.Item($intRow, 4) = $db.AutoShrink

$Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor

$Sheet.Cells.Item($intRow, 5) = $db.RecoveryModel

$Sheet.Cells.Item($intRow, 6) = "{0:N3}" -f $db.Size

#Change the background color of the Cell depending on the SpaceAvailable property value

if ($dbSpaceAvailable -lt 1.00)

{

$fgColor = 3

}

else

{

$fgColor = 0

}

$Sheet.Cells.Item($intRow, 7) = $dbSpaceAvailable

$Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor

$intRow ++

}

$intRow ++

}

$Sheet.UsedRange.EntireColumn.AutoFit()

cls