Learn more about SQL Server tools

   
   















































Retrieve a List of SQL Server Databases and their Properties using PowerShell

MSSQLTips author Edwin Sarmiento By:   |   Read Comments (27)   |   Related Tips: More > PowerShell

Problem
In a previous tip on using Using PowerShell with SQL Server Management Objects (SMO), you've seen how you can use Windows PowerShell and SMO to administer SQL Server databases. I would like to translate some of the Transact-SQL scripts that I use every day, starting with the simple ones like retrieving a list of databases and their properties for auditing purposes.

Solution
One of the things that we do as DBAs is to retrieve a list of databases and their properties for auditing and reporting purposes. We check for properties such as recovery model, available free space, autoshrink, etc., and generate action items based on them. We've already seen how to access the Server object - its properties and methods - using SMO. We will dig into the object hierarchy and look at the different members of the Server object. A SQL Server instance can be described using different properties like instance name, logins, settings, all of which are members of the Server object.

What would be of main interest for this tip is the Databases property. This property represents a collection of database objects defined on a SQL Server instance, regardless whether they are system or user databases. Let's start by retrieving all the members of the Databases property. The script below is the same as the one in the previous scripts with the addition of a Get-Member cmdlet to retrieve the list of members for the Databases property, specifying Property as the MemberType which just means to retrieve all the database properties. Be sure not to get confused with the terms - the Databases property of the Server object is a collection of database objects and the database objects themselves have their own properties.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2000"

$dbs=$s.Databases
$dbs | Get-Member -MemberType Property

From the list of properties from the Database object, let's select the most common ones - Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size and SpaceAvailable

$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable

Notice that it is pretty simple and straight-forward. Since the $dbs variable contains a collection of database objects, you can now pipe the results so a Select-Object cmdlet, which we just refer to as SELECT. You can then make this script dynamic by introducing variables and saving it as a PowerShell script which we have done in previous tips.

Reading from a Text File

There are a couple of ways to list the servers that you manage, but for simplicity's sake, we'll use a simple text file. What we will do is store the names of the SQL Server instances that you administer in a text file named SQL_Servers.txt and pass the contents in a variable named $instance. We will use the Get-Content cmdlet to read thru the contents of the text file and the ForEach-Object cmdlet to iterate thru the collection. The script would look something like the one below. You can either write it in a script file or type it directly in the PowerShell console

ForEach ($instance in Get-Content "D:\SQL_Servers.txt")
{
       [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
       $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

       $dbs=$s.Databases
       $dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable
}

Generate Fancy Reports - Enter Microsoft Excel

Let's admit it, Microsoft Excel happens to be the most popular application to generate spreadsheets and reports so it does make sense to make the most out of it with any reports that we need to generate. IT Managers and key stakeholders would like to see either graphs with colors instead of just numerical values within reports. What we will do is to use PowerShell to automate report creation for our database audit report with Excel. There are a few key things that we need to understand about the process. First, we will be calling Excel thru COM automation and not as a .NET application. We used the New-Object cmdlet to create an instance of the Excel object, the -ComObject parameter to create a new COM object of type Excel.Application which is the ProgID of the object we want to create and assign it the variable $Excel.

$Excel = New-Object -ComObject Excel.Application

You might be interested to find out what other COM components are available on a Windows machine for you to use. To retrieve their ProgID values, you can query the registry with a one-liner like the one below (this was derived from the PowerShell team blog)

dir  "REGISTRY::HKEY_CLASSES_ROOT\CLSID" -include PROGID -recurse | foreach {$_.GetValue("")}

So much for COM objects as we only need the Excel.Application at this point. Next, we set the Visible property of the Excel object to True so we can see what is happening

$Excel.visible = $True

Next, we use the Add() method to add a new workbook to the instance of the Excel object and use the Item property to create an object reference to the first worksheet in the workbook. The variable $Sheet will hold that pointer to the worksheet and will be referred to in most of the code

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

You can add as many workbooks as you want, but one would be enough for our purpose. Now, to actually work with Excel we need to reference row and column numbers. To do that, we call the Cells property of the Excel object to return the cell or cells in the active sheet. We can then modify the cells like providing a value, changing the Font properties, etc. An example to assign a value to a cell in row 1, column 1 and change the Font property to Bold is shown below.

$Sheet.Cells.Item(1,1) = "SQL SERVER INSTANCE NAME:"
$Sheet.Cells.Item(1,1).Font.Bold = $True

Saving the code and running it in PowerShell will open up an Excel spreadsheet that will look something like the one below

You can either call the SaveAs() method of the Worksheet object $Sheet to save the Excel spreadsheet or just leave it as it is.

Imagine dealing with row and column values that are dynamic since there would be a fair amount of databases on each of your SQL Server instances. Below is a PowerShell script that takes into account what we have just covered with SMO and Excel to create a fancy report

#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 "D:\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

     #$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable

     #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

Running the script in PowerShell will generate an Excel spreadsheet similar to the one below, highlighting those properties that need immediate attention using some fancy colors from the definitions available here.

Next Steps



Last Update: 5/26/2009


About the author
MSSQLTips author Edwin Sarmiento
Edwin Sarmiento works as a SQL Server DBA for The Pythian Group in Ottawa and is a SQL Server MVP.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, December 15, 2014 - 2:40:13 PM - bass_player Read The Tip

Hi Mahesh,

I noticed that you are on the Tables object inside the AdventureWorks2014 database. Is Created a property of the Table class?

$sheet.cells.Item($counter,3) = $_.created

I doubt it is based on this documentation. Use the CreateDate property instead


Monday, December 15, 2014 - 9:18:53 AM - Mahesh Read The Tip

I am not able to insert Created date into excelsheet. datatype of Created date is datetime.

Please find the below code : 

cd sql\PC296353-PC\DEFAULT\databases\adventureworks2014\tables

$FilePath = "D:\test.xlsx"

$SheetName = "sheet2"

$excel = New-Object -ComObject Excel.Application

$excel.Visible = $true

$excel.displayalerts=$false

$workbook = $excel.Workbooks.open($FilePath)

$sheet = $workbook.sheets.item($sheetname)

$counter = 1

$sheet.cells.Item(1,1) = “Name”

$sheet.cells.Item(1,1).Font.Bold= $true

$sheet.cells.Item(1,2) = “Schema”

$sheet.cells.Item(1,2).Font.Bold = $true

$sheet.cells.Item(1,3) = “Created”

$sheet.cells.Item(1,3).Font.Bold = $true

 

ls | ForEach-Object {

    $counter++

    $sheet.cells.Item($counter,1) = $_.Name

    $sheet.cells.Item($counter,2) = $_.schema

    $sheet.cells.Item($counter,3) = $_.created

}

$workbook.saveas($filepath)

$workbook.close()

$excel.quit()

 

Get-Process excel | stop-process -Force


Monday, August 11, 2014 - 11:13:54 AM - bass_player Read The Tip

There are a lot of ways to accomplish this task. One of them is mentioned here

http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx


Monday, August 11, 2014 - 12:19:29 AM - Ajitesh Malhotra Read The Tip

Nice job,

Hi,

ForEach ($instance in Get-Content "D:\SQL_Servers.txt")
{

       [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
       $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

       $dbs=$s.Databases
       $dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable
}

I Would like to insert the data in SQL Server table, Could you please help me out for the same.


Friday, December 06, 2013 - 10:37:05 AM - Margaret Read The Tip

I took the loop out and just added the filegroup variable inside the foreach db loop and it is now working properly

 

Thanks!


Friday, December 06, 2013 - 9:38:43 AM - Margaret Read The Tip

I need to include information from the filegroups.files for each database. I tried to include another foreach loop inside the foreach($db in $dbs), but it's not writing the information matched to the database associated with it, so I'm sure I have something wrong with that. Can you help me resolve this?

 #Get information about the database files
 ForEach  ($file in $db.FileGroups.Files)

  {
 $flMaxSize=$file.MaxSize
 $flGrowth = $file.Growth
 $flGT = $file.GrowthType

    $sheet.Cells.Item($intRow, 11) =  switch($flMaxSize){-1 {"Unlimited"} default {"{0:N2}" -f($value)}}
    $Sheet.Cells.Item($intRow, 12) = $flGrowth
    $Sheet.Cells.Item($intRow, 13) = $flGT

 $intRow++
 }

 

 

 


Friday, September 06, 2013 - 10:51:43 AM - bass_player Read The Tip

Hi Mo,

I would assume that you have databases in your SQL Server instance that are marked as OFFLINE. Use the IsAccessible property to only retrieve databases that are online. For example, change the code $dbs = $s.Databases to something like $dbs = $s.Databases | Where {$_.IsAccessible -eq $true}


Friday, September 06, 2013 - 6:12:57 AM - Mo Read The Tip

Im new to PS and have a newbie type question.

I have the script saved root of c, i have the server text file saved same place.

Windows server 2008 machine. 64 bit.

Im testing the code and trying to run it from Windows Powershell ISE.

so from the command window of PS_ISE - i do this : ./Spaceused_spread.ps1

Then i get a bunch of these

 
You cannot call a method on a null-valued expression.
At C:\temp\Powershell\SpaceUsed_Spread.ps1:95 char:28
+           $Sheet.Cells.Item <<<< ($intRow, 7) = $dbSpaceAvailable
    + CategoryInfo          : InvalidOperation: (Item:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
 
You cannot call a method on a null-valued expression.
At C:\temp\Powershell\SpaceUsed_Spread.ps1:96 char:28
+           $Sheet.Cells.item <<<< ($intRow, 7).Interior.ColorIndex = $fgColor
    + CategoryInfo          : InvalidOperation: (item:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull


Monday, July 01, 2013 - 3:21:47 AM - CLETO Read The Tip

Thanks for a great article. I am new to PS

The script works fine for default port 1433, would like to know how to use the same script with a different port 1333

Some server, I get the message failed to connect.

 


Friday, April 26, 2013 - 12:13:29 PM - bass_player Read The Tip

Hi Kalebb,

Instead of querying the MSDB database to retrieve the list of SQL Server Agent jobs, you can use the JobServer property of the Server object in SMO and retrieve the Jobs collection. From there, you can explore the different object properties to retrieve schedules, frequency, etc. Check out this article for more information.

http://www.mssqltips.com/sqlservertip/1798/checking-sql-server-agent-jobs-using-windows-powershell/


Friday, April 26, 2013 - 12:35:33 AM - Kalebb Read The Tip

I tried to modify this script to retrieve a list of jubs running on each instance, but I cannot get to make this work. I'm new to Powershell so a liitle help would be really appreciated.

The Script I am using is:

#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:\Instances.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) = "Job Name"
      $Sheet.Cells.Item($intRow,2) = "Job Enabled"
      $Sheet.Cells.Item($intRow,3) = "Frequency"
      $Sheet.Cells.Item($intRow,4) = "Start Date"
      $Sheet.Cells.Item($intRow,5) = "Start Time"
      $Sheet.Cells.Item($intRow,6) = "Max Duration"
      $Sheet.Cells.Item($intRow,7) = "Subday Frequency"

     #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

     #$dbs | "USE msdb
Go


SELECT dbo.sysjobs.Name AS 'Job Name',
 'Job Enabled' = CASE dbo.sysjobs.Enabled
  WHEN 1 THEN 'Yes'
  WHEN 0 THEN 'No'
 END,
 'Frequency' = CASE dbo.sysschedules.freq_type
  WHEN 1 THEN 'Once'
  WHEN 4 THEN 'Daily'
  WHEN 8 THEN 'Weekly'
  WHEN 16 THEN 'Monthly'
  WHEN 32 THEN 'Monthly relative'
  WHEN 64 THEN 'When SQLServer Agent starts'
 END,
 'Start Date' = CASE active_start_date
  WHEN 0 THEN null
  ELSE
  substring(convert(varchar(15),active_start_date),1,4) + '/' +
  substring(convert(varchar(15),active_start_date),5,2) + '/' +
  substring(convert(varchar(15),active_start_date),7,2)
 END,
 'Start Time' = CASE len(active_start_time)
  WHEN 1 THEN cast('00:00:0' + right(active_start_time,2) as char(8))
  WHEN 2 THEN cast('00:00:' + right(active_start_time,2) as char(8))
  WHEN 3 THEN cast('00:0'
    + Left(right(active_start_time,3),1) 
    +':' + right(active_start_time,2) as char (8))
  WHEN 4 THEN cast('00:'
    + Left(right(active_start_time,4),2) 
    +':' + right(active_start_time,2) as char (8))
  WHEN 5 THEN cast('0'
    + Left(right(active_start_time,5),1)
    +':' + Left(right(active_start_time,4),2) 
    +':' + right(active_start_time,2) as char (8))
  WHEN 6 THEN cast(Left(right(active_start_time,6),2)
    +':' + Left(right(active_start_time,4),2) 
    +':' + right(active_start_time,2) as char (8))
 END,
-- active_start_time as 'Start Time',
 CASE len(run_duration)
  WHEN 1 THEN cast('00:00:0'
    + cast(run_duration as char) as char (8))
  WHEN 2 THEN cast('00:00:'
    + cast(run_duration as char) as char (8))
  WHEN 3 THEN cast('00:0'
    + Left(right(run_duration,3),1) 
    +':' + right(run_duration,2) as char (8))
  WHEN 4 THEN cast('00:'
    + Left(right(run_duration,4),2) 
    +':' + right(run_duration,2) as char (8))
  WHEN 5 THEN cast('0'
    + Left(right(run_duration,5),1)
    +':' + Left(right(run_duration,4),2) 
    +':' + right(run_duration,2) as char (8))
  WHEN 6 THEN cast(Left(right(run_duration,6),2)
    +':' + Left(right(run_duration,4),2) 
    +':' + right(run_duration,2) as char (8))
 END as 'Max Duration',
    CASE(dbo.sysschedules.freq_subday_interval)
  WHEN 0 THEN 'Once'
  ELSE cast('Every '
    + right(dbo.sysschedules.freq_subday_interval,2)
    + ' '
    +     CASE(dbo.sysschedules.freq_subday_type)
       WHEN 1 THEN 'Once'
       WHEN 4 THEN 'Minutes'
       WHEN 8 THEN 'Hours'
      END as char(16))
    END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules
ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
  FROM dbo.sysjobhistory
  GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time = 0

UNION

SELECT dbo.sysjobs.Name AS 'Job Name',
 'Job Enabled' = CASE dbo.sysjobs.Enabled
  WHEN 1 THEN 'Yes'
  WHEN 0 THEN 'No'
 END,
 'Frequency' = CASE dbo.sysschedules.freq_type
  WHEN 1 THEN 'Once'
  WHEN 4 THEN 'Daily'
  WHEN 8 THEN 'Weekly'
  WHEN 16 THEN 'Monthly'
  WHEN 32 THEN 'Monthly relative'
  WHEN 64 THEN 'When SQLServer Agent starts'
 END,
 'Start Date' = CASE next_run_date
  WHEN 0 THEN null
  ELSE
  substring(convert(varchar(15),next_run_date),1,4) + '/' +
  substring(convert(varchar(15),next_run_date),5,2) + '/' +
  substring(convert(varchar(15),next_run_date),7,2)
 END,
 'Start Time' = CASE len(next_run_time)
  WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
  WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
  WHEN 3 THEN cast('00:0'
    + Left(right(next_run_time,3),1) 
    +':' + right(next_run_time,2) as char (8))
  WHEN 4 THEN cast('00:'
    + Left(right(next_run_time,4),2) 
    +':' + right(next_run_time,2) as char (8))
  WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)
    +':' + Left(right(next_run_time,4),2) 
    +':' + right(next_run_time,2) as char (8))
  WHEN 6 THEN cast(Left(right(next_run_time,6),2)
    +':' + Left(right(next_run_time,4),2) 
    +':' + right(next_run_time,2) as char (8))
 END,
-- next_run_time as 'Start Time',
 CASE len(run_duration)
  WHEN 1 THEN cast('00:00:0'
    + cast(run_duration as char) as char (8))
  WHEN 2 THEN cast('00:00:'
    + cast(run_duration as char) as char (8))
  WHEN 3 THEN cast('00:0'
    + Left(right(run_duration,3),1) 
    +':' + right(run_duration,2) as char (8))
  WHEN 4 THEN cast('00:'
    + Left(right(run_duration,4),2) 
    +':' + right(run_duration,2) as char (8))
  WHEN 5 THEN cast('0'
    + Left(right(run_duration,5),1)
    +':' + Left(right(run_duration,4),2) 
    +':' + right(run_duration,2) as char (8))
  WHEN 6 THEN cast(Left(right(run_duration,6),2)
    +':' + Left(right(run_duration,4),2) 
    +':' + right(run_duration,2) as char (8))
 END as 'Max Duration',
    CASE(dbo.sysschedules.freq_subday_interval)
  WHEN 0 THEN 'Once'
  ELSE cast('Every '
    + right(dbo.sysschedules.freq_subday_interval,2)
    + ' '
    +     CASE(dbo.sysschedules.freq_subday_type)
       WHEN 1 THEN 'Once'
       WHEN 4 THEN 'Minutes'
       WHEN 8 THEN 'Hours'
      END as char(16))
    END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
  FROM dbo.sysjobhistory
  GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time <> 0

ORDER BY [Start Date],[Start Time]"

$intRow ++

}

$Sheet.UsedRange.EntireColumn.AutoFit()
cls

Regards.

 


Tuesday, May 08, 2012 - 3:27:30 AM - Prashant Read The Tip

Excellent work! Very useful!!


Tuesday, March 27, 2012 - 6:07:50 PM - bass_player Read The Tip

This is one of the reasons to really get into PowerShell since Microsoft is standardizing server management using PowerShell. I wrote a script a few years ago that does the same thing - go thru all of the servers in the data center and report disks that have free disk space less than the specified threshold and send an email alert - all of this in 3 lines of code.

Glad to know that the scripts are valuable


Tuesday, March 27, 2012 - 4:10:45 PM - Chris Read The Tip

Exactly, that is what I am doing now. I actually have it setup to run on a particular server, fetch all the data into a particular database, and then I use a procedure with HTML to select from the table and send out a Daily Space Check email. It works great!


Tuesday, March 27, 2012 - 2:11:39 PM - Leon Read The Tip

Great example. With a few tweaks, you can turn this code into health check of your sql farm.


Wednesday, March 21, 2012 - 11:25:19 AM - Chris Read The Tip

Thank you very much! I was paying too much attention to the Powershell script and not my own SQL. Very much appreciated.


Tuesday, March 20, 2012 - 5:15:38 PM - Greg Robidoux Read The Tip

Chris,

I haven't used PowerShell much,but try this change

exec ('use ['+@db + ']')


Tuesday, March 20, 2012 - 4:21:58 PM - Chris Read The Tip

I am using the following query to try to check database sizes on each of our servers(fairly similar to what you are doing). It works for all databases except for those that have spaces in their names. I am new to Powershell and have not been able to come up with any way to fix it. Right now, for those that have spaces, it only trys to read the first segment of the name and therefore cannot find the database in the server.databases table. Any help would be appreciated!


[System.Reflection.Assembly]::LoadWithPartialName(”Microsoft.SqlServer.SMO”) | Out-Null
set-executionpolicy RemoteSigned

sqlcmd -S starling\reno_dev -E -Q "truncate table master.dbo.FileStats"

$servers = get-content c:\InstanceList.txt
$query = "DECLARE @db varchar(max)
SET @db = DB_NAME()
exec ('use '+@db)
SELECT CONVERT(sysname, SERVERPROPERTY('ServerName')) AS ServerName
,DB_NAME() AS db
,[fileid],
[File_Size_MB] = convert(decimal(12,2),round([size]/128.000,2))
,[Space_Use_MB] = convert(decimal(12,2),round(fileproperty([name],'SpaceUsed')/128.000,2))
,[Free_Space_MB]=convert(decimal(12,2),round(([size]-fileproperty([name],'SpaceUsed'))/128.000,2))
,[Percent_Free_MB]=CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2))
,CASE WHEN maxsize =-1 THEN 'unlimited'
            WHEN maxsize/128 < 1024 THEN CAST(maxsize/128 as varchar)
            ELSE CAST(maxsize/(128*1024) as varchar)
            END as maxsize
,RTRIM(LTRIM([name])) AS FileName
,RTRIM(LTRIM([filename])) AS FilePath
FROM dbo.sysfiles"

foreach($server in $servers)
{
$sqlserver = new-object “Microsoft.SqlServer.Management.Smo.Server” $server
foreach ($db in $sqlserver.Databases)
{
$db_name=[string]$dbfix
invoke-sqlcmd -Query $query -database $dbfix -ServerInstance $sqlserver.name -IgnoreProviderContext
}
}


Saturday, January 08, 2011 - 7:03:54 AM - Jean-Michel Jarry Read The Tip

Monday, December 20, 2010 - 3:54:46 PM - Susan Van Eyck Read The Tip

Totally awesome tip!  I'm (slowly) working on putting together some PS scripts to keep an eye on my servers and databases.  This gives me a huge head start.  Thanks!


Tuesday, October 20, 2009 - 8:58:40 AM - bass_player Read The Tip

If it is just the output without the Excel thing, you can simply do this. Remember, I am just reading the names of the SQL Server instances from a text file so you can simply use that variable beside the database name

#################################################
#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "D:\SQL_Servers.txt")
{

#GetDBs.ps1
#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

foreach ($db in $dbs)
    {
        Write-Host $instance , $db.Name
    }

}





Saturday, July 25, 2009 - 2:43:32 PM - mzigadlo Read The Tip

Great script.

 I was looking to format my output like this....

server1,db1

server1,db2

server1,db3

server2,db1

but haven't been able to figure out how to get $s.name and $s.databases to output that way.  Any suggestions?


Friday, July 03, 2009 - 7:56:04 AM - Sanjacinto Read The Tip

Hi, I too got the same error. I've found the following code but can't seem to get any further :-(

$xl = New-Object -comobject "excel.application"

$xl.visible = $true

$xlbooks =$xl.workbooks

$newci = [System.Globalization.CultureInfo]"en-GB"

$xlbooks.PSBase.GetType().InvokeMember("Add", [Reflection.BindingFlags]::InvokeMethod, $null, $xlbooks, $null, $newci)

Any ideas how I can incorporate the code from the article below, into the above, so I can reference the $Sheets object? - ($Sheets.Cells.Item() etc)

$Excel = New-Object -ComObject Excel.Application

$Excel.visible = $True

$Excel = $Excel.Workbooks.Add()

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


Thursday, May 28, 2009 - 9:42:01 AM - bass_player Read The Tip

This might be because of the difference between the local user settings and Office settings.  Check the Regional Settings in your user profile or you can include a line in the PowerShell script to call the CultureInfo class but that's a bit geeky

CultureInfo Class

http://msdn.microsoft.com/en-us/library/system.globalization.cultureinfo.aspx 

Here's a Microsoft KB article for this

http://support.microsoft.com/default.aspx?scid=kb;en-us;320369


Thursday, May 28, 2009 - 9:30:40 AM - bass_player Read The Tip

You can add this inside the ForEach loop for the databases collection but you do need to have it formatted properly in Excel as each database would have at least two files

    $fileGroups = $db.FileGroups
    ForEach ($fg in $fileGroups)
    {
        $fg.Files | Select $db.Name, Name, FileName, size, UsedSpace
    }
    $db.LogFiles | Select $db.Name, Name, FileName, Size, UsedSpace


Wednesday, May 27, 2009 - 4:05:22 PM - Jacare Read The Tip

When I try to execute a following statement $Excel = $Excel.Workbooks.Add() in powershell I receive this error:

"Old format
or invalid type library. (Exception from HRESULT: 0x80028018
(TYPE_E_INVDATAREAD))" message..

What can I do to get around this ?


Wednesday, May 27, 2009 - 12:40:11 PM - sqljim Read The Tip

Great tip. I was trying to add the datafiles, log files and their sizes to the export to Excel but, couldn't get the right property. Suggestion on how to get it?




 
Sponsor Information