Welcome‎ > ‎Scripting‎ > ‎

Database script

posted Nov 30, 2010, 7:30 AM by Jurien Bosman
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


Comments